Points to Remember

Some of the Usefull points:-


1) A consistent get is a block gotten in read consistent mode (point in time mode). It MAY or MAY NOT involve reconstruction (rolling back). No. of time a consistent get/read has been requested for a block.

(No.of Rows fetched / arraysize ) + No. of Blocks = No. of Consistent Gets.


2) db block gets are blocks gotten in CURRENT mode -- they were blocks that told us were all of the other blocks were! (they were the segment headers).

3) Set arraysize 100 --No. of rows to be fetched at a time default is 15

More about Logical I/O and Physical I/O , consistent gets etc..

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:880343948514

4) dba_tab_modificatons.
a) alter table emp monitoring;
b) exec dbms_stats.flush_database_monitoring_info;
c) Select the dba_tab_modifications to view the details


5) Re-creating Password file:-

1) Recreate the passwordfile

You can recreate the password file using the ORAPWDxx command by which you can
specify the password for internal. Before running this command to create the
new password file, make sure to delete the old password file. Otherwise, the
ORAPWDxx utility will give an error message.

D:\> ORAPWD file=D:\ORANT\DATABASE\PWDORCL.ORA password=
entries=5

Verify if the new file has been created.

The passwordfile might be hidden. Hidden files can be seen:
on the DOS-prompt by type :
c:\> dir /A:H
in Windows NT explorer :
use the Windows Explorer View/Option 'show all files' to see the
file.


Remark:
-------

If the value of the "remote_login_passwordfile" parameter in the "init.ora" is
EXCLUSIVE then you must shutdown your database before recreating the password
file.

Failure to do so will result in being unable to connect as internal as long as
you don't stop the database by for example stopping services.

2) Alter the password of the user 'SYS' (Read Warning 1)

a) Verify the values of the "remote_login_passwordfile" in the
"init.ora" parameter file.

If its values is exclusive continue with e) if Shared continue with b)

b) Stop the database if it is started

c) Change the "remote_login_passwordfile" in the Init.ora to
"exclusive".

d) Startup the database.

e) Alter the user SYS to a new password, now also the password for
internal.

3) Use ORADIM

You could drop the instance and recreate it with a new password.

Suppose the SID in question is ORCL:

a) You shutdown the database and stop the services.

b) D:\> ORADIM -delete -sid ORCL

c) D:\> ORADIM -new -sid ORCL -intpwd -startmode
auto -pfile D:\ORANT\DATABASE\INITORCL.ORA

-----------
WARNING
-----------

Solution Description:
=====================

II) Most Common Problems
--------------------

1) You're trying to recreate the Password file with ORAPWDxx and
you get the following error:

D:\> ORADIM file=D:\ORANT\DATABASE\PWDORCL.ORA password=ORACLE

OPW-00005: File with same name exists - please delete or rename

Solution:
There is already a file with the same name in the specified
directory. Remove or rename that specific file, before creating the
new one.

The passwordfile might be hidden. Hidden files can be seen:
on the DOS-prompt by type :
c:\> dir /A:H
in Windows NT explorer :
use the Windows Explorer View/Option 'show all files' to see the
file.

2)ORA-1017 or ORA-1031 when trying to connect as internal

[NOTE:114399.1] : WINNT: ORA-1017 WHEN CONNECTING AS INTERNAL ON NT
[NOTE:114401.1] : WINNT: ORA-1031 WHEN CONNECTING AS INTERNAL ON NT

3) ORA-1996 when trying to grant sysdba or sysoper to user

: OERR ORA 1996 GRANT failed password file is full
: ORA-1994 WHEN GRANTING SYSDBA TO A USER

You must create a larger password file to be able to contain more entries
and re-grant the privileges to all of the users.

4) By default in 8.1.x, a new database will use Windows NT Native
Authentication by having the following already set in the "sqlnet.ora" file:

SQLNET.AUTHENTICATION_SERVICES = (NTS)

This will allow any privileged user defined in the NT user group (named
ORA__DBA) to login to database without providing a password. To disable
this feature and force all users to provide a password, simply comment out
the above in the sqlnet.ora file:

#SQLNET.AUTHENTICATION_SERVICES = (NTS)



6) If Creating logical standby database on the same server then when performing any kind of physical file addition/modification then you need to do it in primary then
using the logminer package take the transaction details and skip those transaction in the logical standby database usning the dbms_logstdby.skip_transaction and rerun those statements manually with modification as per the logical standby in the logical standby database.



Errors Faced:-

Problem :- Unable to open the Xserver.

Solution :- The xming is running already, so when i closed it and started using the xlaunch the xming is started and am able to transfer the X server sessions to my desktop.

Logging

Table Mode Insert Mode ArchiveLog mode result
----------- ------------- ----------------- ----------
LOGGING APPEND ARCHIVE LOG redo generated
NOLOGGING APPEND ARCHIVE LOG no redo
LOGGING no append "" redo generated
NOLOGGING no append "" redo generated
LOGGING APPEND noarchive log mode no redo
NOLOGGING APPEND noarchive log mode no redo
LOGGING no append noarchive log mode redo generated
NOLOGGING no append noarchive log mode redo generated


direct path operations (create, alter move, insert /*+ APPEND */, sqlldr direct=y) can skip redo generation in a noarchivelog mode database

more about logging & nologging :
http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:5280714813869


Syntax of With statement:-

SQL> with
a as (select sysdate from dual),
b as (select sysdate from dual)
select * from a, b;

SYSDATE SYSDATE
--------- ---------
03-FEB-11 03-FEB-11

Copying data between databases.

SQL> copy from manzoor/ahamed@firstdb create mytest using select * from test;

Array fetch/bind size is 15. (arraysize is 15)
Will commit when done. (copycommit is 0)
Maximum long size is 80. (long is 80)
Table MYTEST created.

5 rows selected from manzoor@firstdb.
5 rows inserted into MYTEST.
5 rows committed into MYTEST at DEFAULT HOST connection.


Standby Database:-

Problem:-

I have a 10.2.0.1 database and a standby database on another server that has the redo logs applied every 15 mins.

I added a new file to the primary db but the standby server's disk was full so when it tried to automatically it failed.

I've sorted the space issue out but now I get the following error :

ORA-00283: recovery session canceled due to errors
ORA-01111: name for data file 129 is unknown - rename to correct file
ORA-01110: data file 129: 'C:\ORACLE\ORA10G\DATABASE\UNNAMED00129'
ORA-01157: cannot identify/lock data file 129 - see DBWR trace file
ORA-01111: name for data file 129 is unknown - rename to correct file
ORA-01110: data file 129: 'C:\ORACLE\ORA10G\DATABASE\UNNAMED00129'

Solution:-

STEP1:- Check the name of the datafile on standby database

select name from v$datafile; ---- This command should show the datafile name as UNNAMEDxxxxx

STEP2:- ON STANDBY DATABASE
alter system set standby_file_management='manual';

STEP3:- Rename the datafile
alter database create datafile 'C:\ORACLE\ORA10G\DATABASE\UNNAMED00129' as 'C:\ORACLE\ORA10G\DATABASE\actual_dbfile_name.dbf'


STEP4:- On the standby database
alter system set standby_file_management='auto';

STEP5:- On the standby database
recover managed standby database disconnect;

shutdown and then reopen the standby database and continue applying the redo logs

-----------

Problem:-

In Primary database all the datafiles are present in /u01 mount point and also in the
standby database all the datafiles are present in the /u02 mount point.

in standby the db_file_name_convert parameter has been set as below.
db_file_name_convert ='/u01','/u02'

Now i wanted to create a tablespace in the primary with 5g datafile but there is no sufficient space left in the standby database which is having only 2G free space.
But i have another mount point /u03 with 20G space. Inorder to use the /u03 mountpoint i have followed the steps as below. First you need to alter the parameter with the same name of the datafile which we are going to create in the primary.

Note:- if you are going to specify the entire filename then it has to be specified first and then the parent mount point has to be specified else it will try to create the datafile in the paraent mount point and the whold datafile name will be ignored.

In Standby

SQL> alter system set db_file_name_convert =
'/u01/app/oracle/oradata/mydatafile.dbf','/u03/oradata/mydatafile.dbf',
'/u01/app/oracle/oradata','/u02/app/oracle/oradata' scope=spfile;

sql> recover managed standby database cancel;
Media recover complete.

sql> shut immediate;

In primary

sql> create tablespace mytblspc datafile '/u01/app/oracle/oradata/mydatafile.dbf' size 5G;

In standby
sql> startup mount;
sql> alter database recover managed standby database disconnect from session;

Now you can see that only for that particular datafile is been created in /u03 mount point in the standby database.


Datafile Resizing:-

Only possible if there are no blocks at the end of the datafiles.

Eg:-

The test tablespace has been allocated with 100m, we have 3 objects in this tablespace, now we have truncated all these 3 tables, only each table will have only one extent as below.

SQL> select segment_name, extent_id, block_id, blocks, bytes/1024 "KB" froM DBA_EXTENTS WHERE SEGMENT_NAME in ('EMP','EMP1','EMP2') and owner = 'MANZOOR';

SEGMENT_NAME EXTENT_ID BLOCK_ID BLOCKS KB
-------------------- ---------- ---------- ---------- ----------
EMP 0 9 8 64
EMP1 0 12297 8 64
EMP2 0 12681 8 64

As per the above the emp1 and emp2 has the extent nearly at the end of the datafile, so we cannot resize the datafile in this case.

SQL> alter database datafile '/u01/app/oracle/oradata/orclsol/test01.dbf' resize 95m;
alter database datafile '/u01/app/oracle/oradata/orclsol/test01.dbf' resize 95m
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value

Solution:-

Move the tables to another tablespaces temporarily , then resize the datafiles and move them back to the original tablespace.

SQL> alter table manzoor.emp1 move tablespace users;

Table altered.

SQL> alter table manzoor.emp2 move tablespace users;

Table altered.

SQL> alter database datafile '/u01/app/oracle/oradata/orclsol/test01.dbf' resize 5m;

Database altered.

SQL> alter table manzoor.emp1 move tablespace test;

Table altered.

SQL> alter table manzoor.emp2 move tablespace test;

Table altered.

SQL> select segment_name, extent_id, block_id, blocks, bytes/1024 "KB" froM DBA_EXTENTS WHERE SEGMENT_NAME in ('EMP','EMP1','EMP2') and owner = 'MANZOOR';

SEGMENT_NAME EXTENT_ID BLOCK_ID BLOCKS KB
-------------------- ---------- ---------- ---------- ----------
EMP 0 9 8 64
EMP1 0 17 8 64
EMP2 0 25 8 64



Problem:-

One instance is running in linux host system, am not able to connect from the windows system using the OEM tool, checked that the dbconsole is already started and running in linux server.

Solution:-

Have droped and recreated the repository.

$ emca -deconfig all db -repos drop

STARTED EMCA at Mar 2, 2011 11:17:11 AM
EM Configuration Assistant, Version 10.2.0.1.0 Production
Copyright (c) 2003, 2005, Oracle. All rights reserved.

Enter the following information:
Database SID: orclsol
Listener port number: 1521
Password for SYS user:
Password for SYSMAN user:
Password for SYSMAN user:
Do you wish to continue? [yes(Y)/no(N)]: Y
Mar 2, 2011 11:17:27 AM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /u01/app/oracle/product/10.2.0/db_1/cfgtoollogs/emca/orclsol/emca_2011-03-02_11-17-10-AM.log.
Mar 2, 2011 11:17:28 AM oracle.sysman.emcp.util.DBControlUtil stopOMS
INFO: Stopping Database Control (this may take a while) ...
Mar 2, 2011 11:17:30 AM oracle.sysman.emcp.EMReposConfig dropRepository
INFO: Dropping the EM repository (this may take a while) ...
Mar 2, 2011 11:18:25 AM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully dropped
Enterprise Manager configuration completed successfully
FINISHED EMCA at Mar 2, 2011 11:18:25 AM

$ emca -config all db -repos create

STARTED EMCA at Mar 2, 2011 11:18:38 AM
EM Configuration Assistant, Version 10.2.0.1.0 Production
Copyright (c) 2003, 2005, Oracle. All rights reserved.

Enter the following information:
Database SID: orclsol
Central agent home: /u01/app/oracle/product/10.2.0/db_1
Listener port number: 1521
Password for SYS user:
Password for DBSNMP user:
Password for SYSMAN user:
Password for SYSMAN user: Email address for notifications (optional):
Outgoing Mail (SMTP) server for notifications (optional):
-----------------------------------------------------------------

You have specified the following settings

Database ORACLE_HOME ................ /u01/app/oracle/product/10.2.0/db_1

Database hostname ................ mysolaris
Listener port number ................ 1521
Database SID ................ orclsol
Central agent home ................ /u01/app/oracle/product/10.2.0/db_1
Email address for notifications ...............
Outgoing Mail (SMTP) server for notifications ...............

-----------------------------------------------------------------
Do you wish to continue? [yes(Y)/no(N)]: Y
Mar 2, 2011 11:19:26 AM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /u01/app/oracle/product/10.2.0/db_1/cfgtoollogs/emca/orclsol/emca_2011-03-02_11-18-38-AM.log.
Mar 2, 2011 11:19:27 AM oracle.sysman.emcp.EMReposConfig createRepository
INFO: Creating the EM repository (this may take a while) ...
Mar 2, 2011 11:21:43 AM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully created
Mar 2, 2011 11:21:50 AM oracle.sysman.emcp.util.CentralAgentUtil registerWithCentralAgents
INFO: Registering target(s) with central agent(s) (this may take a while)...
Mar 2, 2011 11:21:59 AM oracle.sysman.emcp.util.DBControlUtil startOMS
INFO: Starting Database Control (this may take a while) ...
Mar 2, 2011 11:23:36 AM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: Database Control started successfully
Mar 2, 2011 11:23:36 AM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: >>>>>>>>>>> The Database Control URL is http://mysolaris:1158/em <<<<<<<<<<<
Enterprise Manager configuration completed successfully
FINISHED EMCA at Mar 2, 2011 11:23:36 AM


$ emctl status dbconsole.

Oracle Enterprise Manager 10g Database Control Release 10.2.0.2.0
Copyright (c) 1996, 2005 Oracle Corporation. All rights reserved.
http://mysolaris:1158/em/console/aboutApplication
Oracle Enterprise Manager 10g is running.
------------------------------------------------------------------
Logs are generated in directory /u01/app/oracle/product/10.2.0/db_1/mysolaris_orclsol/sysman/log


Now am able to connect to the database using OEM.


Oralce Best Practices:-
Arup Nanda has worked exclusively as an Oracle DBA for
more than 13 years and won Oracle's DBA of the Year award in 2003.

Based on his Collaborate '07 presentation titled "DBA Best Practices from the Field," this tip provides criteria for determining what makes a "best practice" as well as Nanda's top five best practices for the Oracle DBA.

In my view, a best practice can only be a best practice if it
1) can be justified as to why it's advantageous and
2) can be flexible to adapt to multiple situations. The following five essential best practices were derived from my years of experience working with Oracle systems large and small.

#1: Multiple Oracle Homes

My favorite best practice is the one about multiple Oracle Homes. Here it how it goes. When applying a patch or a patchset, I recommend against applying to the existing Oracle Home. Instead, I suggest creating a new Oracle Home, and apply the patches there.

I create the first Oracle Home at /app/oracle/db_1, for instance. When a patch comes out, I install the whole Oracle
software in a different home -- /app/oracle/db_2 -- and then apply the patch there.
During the process of installation and patch application, the database is up and running, as it runs off the home /db_1. When the outage window comes, all I have to do is to shut down Oracle; change Oracle Home to db_2 and bring the database up. If there is a problem, I can reset the Oracle Home back to the old one.

So, here is the conventional approach:

1.Shut down the database
2.Apply patch to the Oracle Home
3.Start the database
4.In case of problems:
5.Shut down the database
6.Roll back the patch
7.Start the database

Steps 2 and 6 could take as much as three hours depending on the amount of patching. The database is down during these times.
In the new approach:

1.Install new Oracle Home
2.Apply the patch to the new Home
3.Shut down the database
4.Change Oracle Home to the new location
5.Start the database
6.In case of problems:
7.Shut down the database
8.Change Oracle Home to the old one
9.Start the database
The database is down only during steps 4 and 8, which takes a couple of minutes at the most, not hours.

So, here are the advantages:

1.The downtime is significantly reduced, to one 60th of the original time.
2.The risk is reduced significantly, as there is no rolling back a patch; you just go to the older version.
3.You can perform a "diff" on these two homes to see what changed. You can see the differences across multiple homes as well.
4.You can take several databases running on the same server to the new Oracle Home one by one.
5.You can see the various Oracle Homes and what patch level they are on using the inventory.
The only negative part is the space consumption -- you need space for two Oracle Homes. But considering a typical Oracle Home takes
about 4 GB or less, this aspect of the suggestion is trivial.
#2: Set audit trail to DB

Set the audit trail to DB by placing the parameter AUDIT_TRAIL = DB in the initialization parameter file during the database creation.
Setting this parameter does not start the auditing, because an explicit AUDIT command must be given on the object. But the parameter
must be set to a value other than FALSE (the default) for the command to take effect. Being a non-dynamic parameter, the database must
be bounced to change the value of AUDIT_TRAIL. To save the trouble and avoid an outage, always set the value to DB, even if you never
intend to audit anything. It does not break anything and you will always be ready to audit when the time comes.

#3: Don't use .log

Don't use .log as the extension of redo logs. Someone may run a script to remove all the log files assuming they are redundant
and you will end up losing the online redo logs as well, forcing a database recovery. Instead, name them with extension "redo" or "rdo."

#4: Preview RMAN Restore

Preview RMAN Restore to identify all the various backup pieces that will be used in the recovery process without doing an actual recovery.
This eliminates any surprises from missing pieces during an actual recovery process.

#5: Create a new Oracle user for clients running on the same server as the DB

The Oracle Database server software also contains the client piece, which allows the clients to connect to the database on the same server.
But as a best practice do not use the same user or the software; use a new one. For instance, if "oracle" is the user to install Oracle software, create a new user called, say, "oraapp" and install the client-only software using that user. The user "oraapp" should not be part of the dba or the oinstall group; so this user can't log on to the database as sysdba. Create a new group called "appgrp" and assign the user oraaap to this group.

All the application users on the box should also be part of the appgrp group. This way they can use the sqlplus, sqlldr and other executables on the server,
but not be able to connect as sysdba.

The common practice is to use the client software in the same user as the database software owner; but starting with 10.2, Oracle has changed the security policy that takes away the global execution permissions from the Oracle Home. So the only option is to let app users be part of the dba group or change the permissions on Oracle Home -- both make the database extremely vulnerable.

------------------------------------------------------

Problem :- Unable to connect to the database from the client, we have set the oracle_home and found that all the parameters are ok, but still we are not able to connect to the db, we get the error as TNS failed to resolve the service name.

Solution:-

After checking all we found that in sqlnet.ora the NAMES.DEFAULT_DOMAIN has been set but in the tnsnames.ora file we are not using any netservice names with the domain, hence whenver we ping the tnsping automatically the default_domain is been added to the netservice names and that particular netservice name will be searched in the tnsnames.ora file, since the netserivce name with domain name is not present we got this error.

Hence we have commeneted the line in the sqlnet.ora parameter and then it works.

-------------------------------------------------------------------------



Problem:

Unable to connnect to the database using sqlplus / as sysdba even as a admin user.

Solution:-

C:\>sqlplus "/AS SYSDBA"
SQL*Plus: Release 10.2.0.3.0 - Production on Tue May 6 04:42:32 2008
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
ERROR:
ORA-01031: insufficient privileges
Enter user-name:

If you are getting the above error when you try to login with "sqlplus /AS SYSDBA", please check if the following are properly set.
1] The Windows user ID with which you signed in is part of the ORA_DBA group.
2] SQLNET.AUTHENTICATION_SERVICES=(NTS) in sqlnet.ora file.

Now try again.....

C:\>sqlplus "/AS SYSDBA"
SQL*Plus: Release 10.2.0.3.0 - Production on Tue May 6 04:49:13 2008
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the OLAP and Data Mining options
SQL> show user
USER is "SYS"


How to check the User group in Windows:-

c:\> net user administrator

User name Administrator
Full Name
Comment Built-in account for administering the computer/domain
User's comment
Country code 000 (System Default)
Account active Yes
Account expires Never

Password last set 12/20/2010 3:58 PM
Password expires Never
Password changeable 12/20/2010 3:58 PM
Password required Yes
User may change password Yes

Workstations allowed All
Logon script
User profile
Home directory
Last logon 5/5/2011 8:17 AM

Logon hours allowed All

Local Group Memberships *Administrators *Debugger Users
*ora_dba
Global Group memberships *None
The command completed successfully.


In Linux

$ id oracle


CPU Usage.

cpu used by this sesision - 5448
The value is given in hsec we need to divide it by 100 to get number of seconds.
5448/100 = 54 Seconds


Difference between RBO and CB

Please tell me the difference between rule-based and cost-based optimization of SQL queries, in detail. Thank you.
A long time ago, the only optimizer in the Oracle database was the Rule-Based Optimizer (RBO). Basically, the RBO used a set of rules to determine how to execute a query. If an index was available on a table, the RBO rules said to always use the index. There are some cases where the use of an index slowed down a query. For example, assume someone put an index on the GENDER column, which holds one of two values, MALE and FEMALE. Then someone issues the following query:
SELECT * FROM emp WHERE gender='FEMALE';
If the above query returned approximately 50% of the rows, then using an index would actually slow things down. It would be faster to read the entire table and throw away all rows that have MALE values. Experts in Oracle query optimization have come to a rule of thumb that says if the number of rows returned is more than 5-10% of the total table volume, using an index would slow things down. The RBO would always use an index if present because its rules said to.

It became obvious that the RBO, armed with its set of discrete rules, did not always make great decisions. The biggest problem with the RBO was that it did not take the data distribution into account. So the Cost-Based Optimizer (CBO) was born. The CBO uses statistics about the table, its indexes and the data distribution to make better informed decisions. Using our previous example, assume that the company has employees that are 95% female and 5% male. If you query for females, then you do not want to use the index. If you query for males, then you would like to use the index. The CBO has information at hand to help make these kind of determinations that were not available in the old RBO.



DBWR_IO_SLAVES vs DB_WRITER_PROCESSES
Questions about multiple DBWR processes have plagued DBAs since Oracle7. You configured multiple DBWR process in Oracle7 by setting the parameter DB_WRITERS. In Oracle7, multiple DBWR processes were actually slave processes that were unable to perform asynchronous I/O calls on their own. The algorithm used by the Oracle7 DBWR caused it to incur waits when the delay of a single write caused additional writes to queue up until the initial write was complete.

Oracle8 and later release’s DBWR architecture corrects this problem. Oracle’s DBWR now writes continuously without waiting for previous writes to complete. The new design allows DBWR to act as if it were inherently synchronous, regardless of whether the operating system supports asynchronous I/O or not. Administrators are able to configure multiple DBWR process by setting the init.ora parameter db_writer_processes. Multiple database writers became available in Oracle 8.0.4 and allow true multiple database writes. There is no master-slave relationship as in Version 7.

If you implement database writer I/O slaves by setting the dbwr_io_slaves parameter, you configure a single (master) DBWR process that has slave processes that are subservient to perform asynchronous I/O calls. I/O slaves can also be used to simulate asynchronous I/O on platforms that do not support asynchronous I/O or implement it inefficiently.

You can't activate both multiple DBWRs and I/O slaves. If both parameters are set in the parameter file, dbwr_io_slaves will take precedence.
To determine whether to use multiple DBWn processes or database slaves, follow these guidelines:

Use db_writer_processes for most write intensive applications. One per CPU is the recommended setting.

Use db_writer_processes for databases that have a large data buffer cache.
Use dbwr_io_slaves for applications that are not write intensive and run on operating systems that support asynchronous I/O.

Use dbwr_io_slaves on platforms that do no support asynchronous I/O.
Use dbwr_io_slaves on single CPU systems. Multiple DBWR processes are
CPU intensive.

Points on Oracle SQL Performance Parameters:- By donal bursleon

Oracle Corporation has invested millions of dollars in making the cost-based SQL optimizer (CBO) one of the most sophisticated tools ever created. The job of the CBO is to always choose the most optimal execution plan for any SQL statement.
However, there are some things that the CBO cannot detect, which is where the DBA comes in. The types of SQL statements, the speed of the disks and the load on the CPUs, all affect the "best" execution plan for a SQL statement. For example, the best execution plan at 4:00 A.M. when 16 CPUs are idle may be quite different from the same query at 3:00 P.M. when the system is 90 percent utilized.

Despite the name "Oracle", the CBO is not psychic, and Oracle can never know, a priori, the exact load on the Oracle system. Hence the Oracle professional must adjust the CBO behavior periodically. Most Oracle professionals make these behavior adjustments using the instance-wide CBO behavior parameters such as optimizer_index_cost_adj and optimizer_index_caching.

However, Oracle does not recommend changing the default values for many of these CBO settings because the changes can affect the execution plans for thousands of SQL statements.

Here are some of the major adjustable parameters that influence the behavior of the CBO:


optimizer_index_cost_adj: This parameter alters the costing algorithm for access paths involving indexes. The smaller the value, the cheaper the cost of index access.

optimizer_index_caching: This is the parameter that tells Oracle how much of your index is likely to be in the RAM data buffer cache. The setting for optimizer_index_caching affects the CBO's decision to use an index for a table join (nested loops), or to favor a full-table scan.

optimizer_max_permutations: This controls the maximum number of table join permutations allowed before the CBO is forced to pick a table join order. For a six-way table join, Oracle must evaluate 6-factorial, or 720, possible join orders for the tables.

db_file_multiblock_read_count: When set to a high value, the CBO recognizes that scattered (multi-block) reads may be less expensive than sequential reads. This makes the CBO friendlier to full-table scans.

parallel_automatic_tuning: When set "on", full-table scans are parallelized. Because parallel full-table scans are very fast, the CBO will give a higher cost to index access, and be friendlier to full-table scans.

hash_area_size (if not using pga_aggregate_target): The setting for hash_area_size parameter governs the propensity of the CBO to favor hash joins over nested loop and sort merge table joins.

sort_area_size (if not using pga_aggregate_target): The sort_area_size influences the CBO when deciding whether to perform an index access or a sort of the result set. The higher the value for sort_area_size, the more likely that a sort will be performed in RAM, and the more likely that the CBO will favor a sort over pre-sorted
index retrieval.

Important Note: Prior to Oracle 10g, adjusting these optimizer parameters was the only way to compensate for sample size issues with dbms_stats. As of 10g, the use of dbms_stats.gather_system_stats and improved sampling within dbms_stats had made adjustments to these parameters far less important. Ceteris Parabus, always adjust CBO statistics before adjusting optimizer parms. For more details on optimizer parameters, see my latest book "Oracle Tuning: The Definitive Reference".

The parameter optimizer_index_cost_adj controls the CBO's propensity to favor index scans over full-table scans. As we will see, in a dynamic system, the "ideal" value for optimizer_index_cost_adj may change radically in just a few minutes, as the type of SQL and load on the database changes.

Using optimizer_index_cost_adj

The optimizer_index_cost_adj is the most important parameter of all, and the default setting of 100 is incorrect for most Oracle systems. However, for OLTP systems, resetting this parameter to a smaller value (between 10 and 30) may result in huge performance gains.

10g Note: In Oracle 10g, you can achieve a similar result to reducing the value of optimizer_index_cost_adj by analyzing your workload statistics (dbms_stats.gather_system_stats). Also note that utilizing CPU costing (_optimizer_cost_model) may effect the efficiency of plans with lower values for optimizer_index_cost_adj.

Is it possible to query the Oracle environment and intelligently determine the optimal setting for optimizer_index_cost_adj? Let's examine the issue.

The optimizer_index_cost_adj parameters default to a value of 100, and can range in value from 1 to 10,000. A value of 100 means that equal weight is given to index vs. multiblock reads. In other words, optimizer_index_cost_adj can be thought of as a "how much do I like full-table scans?" parameter.

With a value of 100, the CBO likes full-table scans and index scans equally, and a number lower than 100 tells the CBO that index scans are faster than full-table scans. However, even with a super-low setting (optimizer_index_cost_adj=1), the CBO will still choose full-table scans for no-brainers, like tiny tables that reside on two blocks.

optimizer_index_cost_adj.sql


col c1 heading 'Average Waits for|Full Scan Read I/O' format 9999.999
col c2 heading 'Average Waits for|Index Read I/O' format 9999.999
col c3 heading 'Percent of| I/O Waits|for Full Scans' format 9.99
col c4 heading 'Percent of| I/O Waits|for Index Scans' format 9.99
col c5 heading 'Starting|Value|for|optimizer|index|cost|adj' format 999


select
a.average_wait c1,
b.average_wait c2,
a.total_waits /(a.total_waits + b.total_waits) c3,
b.total_waits /(a.total_waits + b.total_waits) c4,
(b.average_wait / a.average_wait)*100 c5
from
v$system_event a,
v$system_event b
where
a.event = 'db file scattered read'
and
b.event = 'db file sequential read'
;


Here is the output from the script in Listing A.

Starting
Value
for
optimizer
Percent of Percent of index
Average waits for Average waits for I/O waits I/O waits cost
full scan read I/O index read I/O for full scans for index scans adj

------------------ ------------------- ---------------- --------------- -------
1.473 .289 .02 .98 20


As you can see, the suggested starting value for optimizer_index_cost_adj may be too high because 98 percent of the data waits are on index (sequential) block access. How we can "weight" this starting value for optimizer_index_cost_adj to reflect the reality that this system has only two percent waits on full-table scan reads (a typical OLTP system with few full-table scans). As a practical matter, we never want an automated value for optimizer_index_cost_adj to be less than one or more than 100.

Also, these values change constantly, As the I/O waits accumulate and access patterns change, this same script may give a very different result at a different time of the day.


Optimization

The Oracle Cost-based SQL optimizer is one of the world's most sophisticated software achievements, but it is the job of the Oracle professional to provide valid statistics for the schema and understand how the Oracle parameters affect the overall performance of the SQL optimizer. Remember, suboptimal SQL execution plans are a major reason for poorly performing Oracle databases, and because the CBO determines the execution plans, it is a critical component in Oracle optimization.


Thanks to Donald bursleon.

--------------------------------------------------------------------------------


Moving data between different versions using exp/imp utility:-
By Tom

To move data DOWN a version(s), you need to export using that lower versions EXP tool and IMP using that lower versions tool.

To move data UP a version, you export using the EXP of the database that contains the data and you IMP using the imp that ships with the TARGET database.

Eg:

to move data from 8.0 to 7.3:

o run catexp7 in the 8.0 database.
o exp73 over sqlnet to the 8.0 database.
o imp73 natively into the 7.3 database.


to move data from 7.3 to 8.1

o exp73 natively on the 7.3 instance
o imp natively using the 8.1 imp.exe



Port Details:-

1521: Default port for the TNS Listener.
1522 – 1540: Commonly used ports for the TNS Listener
1575: Default port for the Oracle Names Server
1630: Default port for the Oracle Connection Manager – client connections
1830: Default port for the Oracle Connection Manager – admin connections
2481: Default port for Oracle JServer/Java VM listener
2482: Default port for Oracle JServer/Java VM listener using SSL
2483: New port for the TNS Listener
2484: New port for the TNS Listener using SSL


-

Use plimit to set the user shell limit in solaris.

Eg:-

plimit $$
plimit -n 65536 $$

X11 Forwarding With Putty Using Xming

X11 forwarding with SSH

X11 Forwarding with ssh is a wonderful feature which allows you get windows of a remotely started applications shown on your own desktop. For Windows, there are lots of pretty good albeit expensive products like Citrix, GoGlobal and XWin32, which allow you access your remote Unix desktop sessions. But in reality, if you don’t need any sessions but only want to remotely start an application and get a window from it on your desktop, you won’t need any of these expensive products – the easiest will be to use X11 forwarding and Xming.

Xming
Xming is an X Windows port for Microsoft Windows. Essentially it’s an X-server which starts transparently on top of your MS Windows desktop. It allows you to redirect graphical output of applications you run on remote Unix servers and therefore see these applications windows on your MS Windows desktop.

Xming is very simple and easy to use. All you have to do is download its distribution archive from the project’s page on SourceForge: XMing @ SourceForge.

Configure SSHd for X11 forwarding
Now that you have Xming installed, start it and it’s time to take care of the ssh side of things. We have to alter the ssh daemon config file: /etc/ssh/sshd_config. Just ensure that it has the following:

# X11 tunneling options
X11Forwarding yes
X11DisplayOffset 10
X11UseLocalhost yes

If you already have similar parameters, don’t do anything. But if you had to change the config file, it’s time to restart ssh:

solaris# svcadm restart svc:/network/ssh

All that’s left now is to download the wonderful free Putty client, that is if you’re not using it yet. You can get it here:
Putty: a free telnet/ssh client.

I won’t go into all the Putty configuration details, I’ll only mention that for X11 forwarding, you have to do the following: in the main configuration window of Putty (you get it when you start Putty), select Connection section, then SSH, then X11, and make sure you tick the Enable X11 Forwarding option and if the Xserver and putty are running on the same machine then enter "localhost:10.0" in the text box provided.

Once logged on using putty check whether the DISPLAY variable has been set, if it is not set then set the DISPLAY variable.

For bash/ksh Shell
export DISPLAY=localhost:10.0

or

For C Shell
set DISPLAY=localhost:10.0

Then check whether we can able to stimulate the X11 program , enter xclock to check.
if we have set all the necessary configurations properly then it will forward the GUI clock to the Windows desktop.. which means now our linux/solaris server will forward the GUI applications to our windows desktop.
----
also check this page and configure you remote host depending on you server.





http://www.netsarang.com/faq/xmanager/list

Unix / Linux Some important CLI / Infos

Dialect of UNIX RAM display command

DEC-UNIX uerf -r 300 | grep -i mem
Solaris prtconf |grep -i mem
AIX lsdev -C|grep mem
Linux free
HP/UX swapinfo -tm

Command to find the no. of cpu:-

Linux :-
cat /proc/cpuinfo | grep processor

Solaris :-

psrinfo



Aix Commands

lsps
Report statistics about paging space.

Example: lsps -a

iostat
Report statistics for ttys, disks and cpu

Examples What it does
iostat -d hdisk0 hdisk1 5
Will monitor disk activity only for physical volumes hdisk0 1
iostat -t 5
Will monitor terminal activity only and display tty statistics every 5 seconds

ipcs

Status of interprocess communication facilities. This is a kernel attribute that cannot be modified.

lsattr
Lists attributes associated with a device.

NOTE: maxmbuf sets the limit of the memory governed by the IPC and network buffering capability.

Examples What it does
lsattr -l sys0 -E
Entries marked 'True' are attributes that can be configured
lsattr -l sys0 -a maxpout=9 -a minpout=6
Changes the HIGH/LOW water marks for pending write I/Os per file.

nfsstat
Display information about NFS and RPC calls

Examples What it does
nfsstat
Displays statistics about NFS and RPC calls
nfsstat -zcsr
Initializes statistics (to ZERO) for client (-c), server (-s), and rpc (-r) calls

pagesize
Display system page size

ps
Display status of current processes. This command is useful for determining if
runaway processes are excessively utilizing the CPU or memory.

Examples What it does
ps avg a displays information about all processes. v displays fields:
PGIN,SIZE,RSS,LIM,TSIZE,TRS,%CPU,%MEM
ps -ft tty2/0 Lists all the processes running on port tty2/0
ps -furoger Lists all the processes running under the user roger
ps -e Display the environment as well as the command
ps -ef Full listing of all processes in the system. If the 'C' field
(means processor utilization) has a large value, this means that
process is a CPU intensive task.
ps -t- Lists processes not associated with a terminal


sar
System Activity Recorder utility found on all UNIX systems that monitors various system functions like

cpu
hard disk
terminal IO
number of files open
processes running

sar -u 5 10 Interactively run the sar command and review CPU
utlization every 10 seconds for 5 interations.

tprof

Detailed profile of CPU usage by an application. Provides an estimate of
CPU usage for each routine in a profile. Useful to determine where an application maybe CPU bound.

vmstat

Statistics about virtual memory and cpu/hard disk usage.

Example: vmstat hdisk0 hdisk1 5 (Display various statistics every 5 seconds)


Linux

free
This command displays statistics on memory usage. The values represent KB's.

Example...

total used free shared buffers cached
Mem: 63208 61484 1724 27848 1060 49360
-/+ buffers/cache: 11064 52144
Swap: 128516 6500 122016


du
This command stands for disk usage and will print the size of the current directory
and subdirectories in 1 KB sizes.

df
This command displays information for each file system.

Example: df

Filesystem 1024-blocks Used Available Capacity Mounted on
/dev/sda1 497667 301229 170736 64% /
/dev/sda2 7469935 5721885 1360884 81% /usr

env
This command lists all shell (environment) variables and their contents for the current user.

ps
This command lists process status. There are many options for this command. Below are the two most common.

Example: ps u (process status for current user)

USER PID %CPU %MEM SIZE RSS TTY STAT START TIME COMMAND
roger 418 0.0 1.7 1808 1124 p0 S 23:27 0:01 -bash
roger 690 0.0 0.5 872 356 p0 R 00:09 0:00 ps u

PID = Unique Process ID
SIZE = Virtual image size; size of text+data+stack
RSS = Resident set size; kilobytes of program in memory
TTY = Controlling tty
STAT = Information about the status of the process
R = runnable
S = sleeping
D = uninterruptible sleep
T = stopped or traced
Z = zombie process
Second field = W if the process has no resident pages.
Third field = N if the process has a positive nice value


top
This command display a full screen of information of the top processes.
It will update automatically until the program is stopped. To stop the display, enter q.

procinfo

gathers some system data from the /proc directory and prints it formatted on the screen.

Last Boot time
Load Average
average number of jobs running
number of runnable processes
total number of processes
PID of the last process run (idem)
Swap info
Memory resources
Number of disks
IRQ info

procinfo -fn30
To exit, simply press q


uptime
This command displays a one line summary showing...

Example: uptime
11:42pm up 18 days, 8:45, 1 user, load average: 0.00, 0.00, 0.00


Solaris

List Solaris Hardware Configuration
$ /usr/sbin/prtconf


Show Swap Space currently installed
Multiply the Blocks column by 512

$ swap -l

How to Display Virtual Memory Statistics (vmstat)

The following example shows the vmstat display of statistics gathered at five-second intervals.

$ vmstat 5


iostat 5

tty md1 md3 md4 md5 cpu
tin tout kps tps serv kps tps serv kps tps serv kps tps serv us sy wt id

How to Check CPU Utilization
#sar

Display CPU utilization with the sar -u command. (The sar command without any options is equivalent to sar -u.) At any given moment, the processor is either busy or idle. When busy, the processor is in either user or system mode. When idle, the processor is either waiting for I/O completion or "sitting still" with no work to do.

Measure CPU utilization during 5 secs one time.

sar -u 5 1


Measure CPU utilization during 60 secs 1440 times and write result in file sar.log.

sar -u -o sar.log 60 1440

To later review disk and tape activity from that period:

sar -d -f sar.log

Determine the memory used by each Oracle background process on a Solaris

This can be used by anyone who has privleges for the pmap, which can be found in /usr/proc/bin/.
First, we need to find the process id (PID) of the Oracle background process you wish to determine the memory size for. This is done by issueing the following command:


# ps -u oracle -f

Virtual / Physical Memory Usage

Solaris is a virtual memory system. The total amount of memory that you can use is
increased by adding swap space to the system. If you ever see "out of memory" messages, adding swap space is the usual fix. Performance of the system is very dependent on how much physical memory (RAM) you have. If you don't have enough RAM to run your workload, performance degrades rapidly.

Physical memory usage can be classified into four groups:

Kernel memory mapped into kernel address space
Process memory is mapped into a process address space
Filesystem cache memory that is not mapped into any address space
Free memory that is not mapped into any address space

RMCmem includes a simple command to summarize this:

# /opt/RMCmem/bin/prtmem
Total physical memory
The total physical memory can be seen using prtconf. Memory is allocated in units called pages, and you can use the 'pagesize' command to see the size in bytes per page:

# /usr/sbin/prtconf | grep Memory
Memory size: 1024 Megabytes

# /usr/bin/pagesize
8192

prtstat -a v


General:-

top

vmstat
$ vmstat 5 3
Displays system statistics (5 seconds apart; 3 times):

procs memory page disk faults cpu

r b w swap free re mf pi po fr de sr s0 s1 s2 s3 in sy cs us sy id
0 0 0 28872 8792 8 5 172 142 210 0 24 3 11 17 2 289 1081 201 14 6 80
0 0 0 102920 1936 1 95 193 6 302 1264 235 12 1 0 3 240 459 211 0 2 97
0 0 0 102800 1960 0 0 0 0 0 464 0 0 0 0 0 107 146 29 0 0 100

Having any processes in the b or w columns is a sign of a problem system.
Having an id of 0 is a sign that the cpu is overburdoned.
Having high values in pi and po show excessive paging.

procs (Reports the number of processes in each of the following states)
r : in run queue
b : blocked for resources (I/O, paging etc.)
w : runnable but swapped

memory (Reports on usage of virtual and real memory)
swap : swap space currently available (Kbytes)
free : size of free list (Kbytes)

page (Reports information about page faults and paging activity (units per second)
re : page reclaims
mf : minor faults
pi : Kbytes paged in
po : Kbytes paged out
fr : Kbytes freed
de : anticipated short-term memory shortfall (Kbytes)
sr : pages scanned by clock algorith

disk (Reports the number of disk operations per second for up to 4 disks

faults (Reports the trap/interupt rates (per second)

in : (non clock) device interupts
si : system calls
cs : CPU context switches

cpu (Reports the breakdown of percentage usage of CPU time (averaged across all CPUs)
us : user time
si : system time
cs : idle time


CPU Usage
sar
$ sar -u 10 8
Reports CPU Utilization (10 seconds apart; 8 times):
Time %usr %sys %wio %idle
11:57:31 72 28 0 0
11:57:41 70 30 0 0
11:57:51 70 30 0 0
11:58:01 68 32 0 0
11:58:11 67 33 0 0
11:58:21 65 28 0 7
11:58:31 73 27 0 0
11:58:41 69 31 0 0
Average 69 30 0 1

#sar -r 5 10


%usr: Percent of CPU in user mode
%sys: Percent of CPU in system mode
%wio: Percent of CPU running idle with a process waiting for block I/O
%idle: Percent of CPU that is idle


mpstat
$ mpstat 10 2
Reports per-processor statistics on Sun Solaris (10 seconds apart; 8 times):
CPU minf mjf xcal intr ithr csw icsw migr smtx srw syscl usr sys wt idl
0 6 8 0 438 237 246 85 0 0 21 8542 23 9 9 59
0 0 29 0 744 544 494 206 0 0 95 110911 65 29 6 0


$ ps -e -o pcpu -o pid -o user -o args | sort -k 1 | tail -21r
Displays the top 20 CPU users on the system.
%CPU PID USER COMMAND
78.1 4789 oracle ora_dbwr_DDDS2
8.5 4793 oracle ora_lgwr_DDDS2
2.4 6206 oracle oracleDDDS2 (LOCAL=NO)
0.1 4797 oracle ora_smon_DDDS2
0.1 6207 oracle oracleDDDS2 (LOCAL=NO)
etc. etc. etc. etc.

The PID column can then be matched with the SPID column on the V$PROCESS view
to provide more information on the process:

SELECT a.username,
a.osuser,
a.program,
spid,
sid,
a.serial#
FROM v$session a,
v$process b
WHERE a.paddr = b.addr
AND spid = '&pid';

unzip to different foler:-

$ unzip file.zip -d /u01/app


CRON

field allowed values
----- --------------
minute 0-59
hour 0-23
day of month 1-31
month 1-12
day of week 0-7 (both 0 and 7 are Sunday)
user Valid OS user
command Valid command or script.

The first 5 fields can be specified using the following rules:

* - All available values or "first-last".
3-4 - A single range representing each possible from the start to the end of the range inclusive.
1,2,5,6 - A specific list of values.
1-3,5-8 - A specific list of ranges.
0-23/2 - Every other value in the specified range.

The following entry runs a cleanup script a 01:00 each Sunday. Any output or errors from the
script are piped to /dev/null to prevent a buildup of mails to root.

0 1 * * 0 /u01/app/oracle/dba/weekly_cleanup > /dev/null 2>&1


Useful Files
Here are some files that may be of use:

Path Contents
/etc/passwd User settings
/etc/group Group settings for users.
/etc/hosts Hostname lookup information.
/etc/system Kernel parameters for Solaris.
/etc/sysconfigtab Kernel parameters for Tru64.

Check the below for more tools

http://www.cyberciti.biz/tips/top-linux-monitoring-tools.html


Calcuting SWAP Space Recommendation:-


Here is my rule for normal server (Web / Mail etc):
Swap space == Equal RAM size (if RAM < 2GB)
Swap space == 2GB size (if RAM > 2GB)

For Oracle server

Swap space == Equal RAM size (if RAM < 8GB)
Swap space == 0.50 times the size of RAM (if RAM > 8GB)

Red Hat minimum swap space recommendation:-

1. Systems with 4GB of ram or less require a minimum of 2GB of swap space
2. Systems with 4GB to 16GB of ram require a minimum of 4GB of swap space
3. Systems with 16GB to 64GB of ram require a minimum of 8GB of swap space
4. Systems with 64GB to 256GB of ram require a minimum of 16GB of swap space

Find the Bit configuration of Server:-

Aix : getconf -a | grep KERN
KERNEL_BITMODE: 64

Solaris :- isainfo -v
32-bit

Linux
Linux users should type the uname command.
Depending on the platform, you may see

$ uname -a
Linux gaylord.stata.com 2.6.11-1.27_FC3 #1 Tue May 17 20:24:57 EDT 2005 x86_64 x86_64 x86_64 GNU/Linux

$ uname -a
Linux caddo.stata.com 2.6.9-5.0.5.EL #1 SMP Fri Apr 8 14:20:58 EDT 2005 ia64 ia64 ia64 GNU/Linux

$ uname -a
Linux tango.stata.com 2.6.10-1.771_FC2smp #1 SMP Mon Mar 28 01:10:51 EST 2005 i686 i686 i386 GNU/Linux

In the above listing, 'gaylord' (x86_64 GNU/Linux) and 'caddo' (ia64 GNU/Linux) are 64-bit compliant. 'tango' (i386 GNU/Linux) is only a 32-bit platform

Windows:-
run-> winmsd -> System x82- 32 bit or ia64 - 64 bit



how to Change the ip in solaris

edit the /etc/hosts file and save it using :wq! and reboot or # svcadm restart network/physical


How to change the hostname in solaris

Change the hostname in the following files:

/etc/nodename
/etc/hostname.*interface
/etc/inet/hosts
/etc/inet/ipnodes

and rename directory under /var/crash

# cd /var/crash
# mv oldname newname

then reboot the server.



Versions of Solaris Explained

SunOS is the core operating system comprising the kernel, utilities and basic libraries. Solaris is the broader environment comprising SunOS, OpenWindows and networking support. In other words, SunOS is a component of Solaris.

SunOS and Solaris relate to each other as follows:

For example, when one does a 'uname -a' this reports that the server has SunOS 5.6 installed which means that it actually got Solaris 2.6 installed. .

SUN OS Version Is Solaris Version
SunOS 5.4 Solaris 2.4
SunOS 5.5 Solaris 2.5
SunOS 5.5.1 Solaris 2.5.1
SunOS 5.6 Solaris 2.6
SunOS 5.7 Solaris 7
SunOS 5.8 Solaris 8
SunOS 5.9 Solaris 9
SunOS 5.10 Solaris 10


How Can we tell Solaris OS is running 32-bit or 64-bit?
Use the isalist command to determine whether the machine is running
the 32-bit or 64-bit operating system. If you are running the 64-bit
operating system on an UltraSPARC machine, then isalist
will list sparcv9 first

How to boot in 64/32 bit mode?
To boot a 32-bit kernel, at the ok prompt type:
ok boot [disk or net] kernel/unix



To boot a 64-bit kernel (default), at the ok prompt type:

ok boot [disk or net] kernel/sparcv9/unix

ok boot [disk or net]

Run job in batch now:
at -s now < thejob.sh

Show current process active
ps -efa

Show process information
psrinfo -v

Show version of unix
uname -a

Display System Configuration
sysdef

or

prtconf

Print VTOC
prtvtoc /dev/dsk/c0t0d0s0

Query Disk space
df -k disk space in kilobytes

du -sk disk space summary in kilobytes

How To Configure Sun 450 Hot swap disk drives
1. drvconfig

2. disks

Remove all files and sub-directories
rm -r *

Move all files from one directory to another using tar pipe
from directory /var

mkdir /var1

cd /var

tar cf - . | (cd /var1 && tar xBf -)

Directory compare (don't show files that are the same)
dircmp -s /var /var1

Give User execute permission on a file
chomod u+x filename gives execute permission to the owner.

Find command to find in current directory and sub directory
find . -name "dbmslogmnr.sql" -print


DATE Command
date mmddHHMM[[cc]yy]

example "date 022610221998"

Get DATE from another unix box
rdate pluto

Find Command for certain size files
find . -size +10000c

This example say find all the file > 10000 bytes.



Find command to find a word in the directory and sub directory
find . -exec grep -ls pkzip {} \;



Linux - Shell Limits to limit user processes (/etc/security/limits.conf)
About Limiting user processes is important for running a stable system. To limit user process, you have just to set shell limit by adding:

a user name
or group name
or all users

to /etc/security/limits.conf file and impose then process limitations.

Example of /etc/security/limits.conf file

* hard nofile 65535
* soft nofile 4096
@student hard nproc 16384
@student soft nproc 2047

A soft limit is like a warning and hard limit is a real max limit. For example, following will prevent anyone in the student group from having more than 50 processes, and a warning will be given at 30 processes.

@student hard nproc 50
@student soft nproc 30

Hard limits are maintained by the kernel while the soft limits are enforced by the shell.

The /etc/security/limits.conf file contains a list line where each line describes a limit for a user in the form of:

Where:

can be:
an user name
a group name, with @group syntax
the wildcard *, for default entry
the wildcard %, can be also used with %group syntax, for maxlogin limit
can have the two values:
“soft” for enforcing the soft limits (soft is like warning)
“hard” for enforcing hard limits (hard is a real max limit)
can be one of the following:
core - limits the core file size (KB)
can be one of the following:
core - limits the core file size (KB)
data - max data size (KB)
fsize - maximum filesize (KB)
memlock - max locked-in-memory address space (KB)
nofile - Maximum number of open file descriptors
rss - max resident set size (KB)
stack - max stack size (KB) - Maximum size of the stack segment of the process
cpu - max CPU time (MIN)
nproc - Maximum number of processes available to a single user
as - address space limit
maxlogins - max number of logins for this user
maxsyslogins - max number of logins on the system
priority - the priority to run user process with
ocks - max number of file locks the user can hold
sigpending - max number of pending signals
msgqueue - max memory used by POSIX message queues (bytes)
nice - max nice priority allowed to raise to
rtprio - max realtime priority
chroot - change root to directory (Debian-specific)

How to
Set the limitations
Open the /etc/security/limits.conf file and change the existing values for “hard” and “soft” parameters as it's given in your installation documentation.
Restart the system after making changes.
If the current value for any parameter is higher than the value listed in the installation document, then do not change the value of that parameter.

* hard nofile 65535
* soft nofile 4096
* hard nproc 16384
* soft nproc 2047Verify the limitations
To check the soft and hard limits, log as the user and enter the following ulimit command:

Limitation Soft Hard
file descriptor ulimit -Sn ulimit -Hn
number of processes available to a user ulimit -Su ulimit -Hu
stack ulimit -Ss ulimit -Hs



---
Download the rpm packages for linux os from
http://linux1.fnal.gov/linux/lts47/i386/SL/RPMS/repodata/repoview/glibc-common-0-2.3.4-2.41.html

Probelm:-
I am unable to install glibc-2.3.4-2.41.i386.rpm
[root@test ank]# rpm -Uvh glibc-2.3.4-2.41.i386.rpm
warning: glibc-2.3.4-2.41.i386.rpm: V3 DSA signature: NOKEY, key ID b38a8516
error: Failed dependencies:
glibc-common = 2.3.4-2.41 is needed by glibc-2.3.4-2.41.i386
glibc = 2.3.4-2.39 is needed by (installed) glibc-headers-2.3.4-2.39.i386
glibc = 2.3.4-2.39 is needed by (installed) glibc-devel-2.3.4-2.39.i386
glibc = 2.3.4-2.39 is needed by (installed) glibc-utils-2.3.4

Solution:-
rpm -Uvh glibc-2.3.4-2.41.i386.rpm --nodeps

Virtual Private Database (VPD) 10g

The VPD is used to restrict the user from viewing/selecting certain rows/columns. The DBMS_rls package manage the Row level security in the database.

When a user directly or indirectly accesses a table view, or synonym associated with a vpd security policy. The oracle database serer automatically modifies the users sql statement to filter the rows. The modifications is banes on the where clause returned by a function , which implements the security policy.

The following are the new features for VPD in 10g

* column level privacy / column masking
* Static, context-sensitive and shared policies
* Support for paralled queries.

Column Level VPD:-

Using sec_relevant_cols to the dbms_rls.add_policy procedure. If this parameter is omitted then the policy behaves as in oracle 9i, when the policy is applied to all columns.

The column level privacy enforces row-leve-access control only when a statement accesses security relevant columns.

Eg.

SQL> select * from emp;

EMPID ENAME DEPTID SALARY
---------- ------------------------------ ---------- ----------
1000 Raja 1 40000
1001 Jameel 2 42000
1002 Ahamed 3 44000
1004 Kannan 2 45000

Here no we want to restrict the access of columns if the department no is not 2 , means it will list out only the rows if the department is 2.

We can accomplish this task using below.

I) Create the function:-

SQL> create or replace function listonly2
(v_owner varchar2, v_objname varchar2) return varchar2 as
where_clause varchar2(200);
begin
where_clause := 'deptid=2';
return where_clause;
end;
/

II) Create the policy:-

SQL> begin
sys.dbms_rls.add_policy
(
object_schema => 'MANZOOR',
object_name => 'EMP',
policy_name => 'LISTDEPT2',
function_schema => 'MANZOOR',
policy_function => 'LISTONLY2',
sec_relevant_cols => 'SALARY'
);
end;
/

Here we have created a policy in such a way that whenever the salary column is selected it needs to retrun only the rows for which the department id is 2 and if the salary column is not included in the selection then it will list all the rows.

See below..

SQL> select * from emp;

EMPID ENAME DEPTID SALARY
---------- ------------------------------ ---------- ----------
1001 Jameel 2 42000
1004 Kannan 2 45000

The above returns the rows for which the deptid is 2 since the salary column has been included in the selection list.

SQL> select empid, ename, deptid from emp;

EMPID ENAME DEPTID
---------- ------------------------------ ----------
1000 Raja 1
1001 Jameel 2
1002 Ahamed 3
1004 Kannan 2

The above returns all the rows in the emp table since we have not included the salary column in the selection list.

Another eg.

In this only the data pertaining to the current user should list if the salary column is included in the selection list, and if the user is selecting all the columns excluding the salary column then it will list all the rows.

SQL> select * from emp;

EMPID ENAME DEPTID SALARY
---------- ------------------------------ ---------- ----------
1000 Raja 1 40000
1001 Jameel 2 42000
1002 Ahamed 3 44000
1004 Kannan 2 45000

SQL> create or replace function listcuruser (v_owner varchar2, v_objnam varchar2) return varchar2 as
where_clause varchar2(50);
begin
where_clause := 'ename=sys_context(''USERENV'',''SESSION_USER'')';
return where_clause;
end;
/

SQL> begin
sys.dbms_rls.add_policy
(
object_schema => 'MANZOOR',
object_name => 'EMP',
policy_name => 'LISTONLYUSER',
function_Schema => 'MANZOOR',
policy_function => 'LISTCURUSER',
sec_relevant_cols => 'SALARY'
);
end;
/

SQL> show user;
USER is "MANZOOR"

SQL> select * from emp;

no rows selected


--The above returns no rows since the user manzoor is not existing the the emp table and he is selected all the columns i.e. including the salary colunm.

SQL> select empid, ename, deptid from emp;

EMPID ENAME DEPTID
---------- ------------------------------ ----------
1000 RAJA 1
1001 JAMEEL 2
1002 AHAMED 3
1004 KANNAN 2

The above retruns all the rows since the user does not included the salary column in the selection list;

SQL> conn ahamed/ahamed;
Connected.
SQL> show user;
USER is "AHAMED"

SQL> select * from manzoor.emp;

no rows selected

--The above returns no since currently the emp table has two policy the 1st is to select only the deptid is 2 and the 2nd is to select only the records pertaining to that particular user. Since the deptid of AHAMED is 3 it does not return any rows.

SQL> conn manzoor/ahamed;
Connected.

SQL> update emp set deptid = 2 where ename ='AHAMED';

1 row updated.

SQL> commit;

Commit complete.

SQL> conn ahamed/ahamed;
Connected.

SQL> select * from manzoor.emp;

EMPID ENAME DEPTID SALARY
---------- ------------------------------ ---------- ----------
1002 AHAMED 2 44000


To drop the policies-

SQL> begin
dbms_rls.drop_policy
(
object_schema => 'MANZOOR',
object_name => 'EMP',
policy_name => 'LISTONLYUSER'
);
end;
/

PL/SQL procedure successfully completed.

SQL> begin
dbms_rls.drop_policy
(
object_schema => 'MANZOOR',
object_name => 'EMP',
policy_name => 'LISTDEPT2'
);
end;
/


Columns Masking

Here it will list all the rows but it will hide or mask the column that we have mentioned in the policy. This can be accomplished using dbms_rls.add_policy package/procedure and packing by parameter sec_relevant_cols_opt => dbms_rls_all_rows.

Eg:-

SQL> begin
dbms_rls.add_policy
(
object_schema => 'MANZOOR',
object_name => 'EMP',
policy_name => 'COLMASKING',
function_schema => 'MANZOOR',
policy_function => 'LISTONLY2',
sec_relevant_cols => 'SALARY',
sec_relevant_cols_opt => DBMS_RLS.ALL_ROWS
);
END;
/

PL/SQL procedure successfully completed.

SQL> select * from emp;

EMPID ENAME DEPTID SALARY
---------- ------------------------------ ---------- ----------
1000 RAJA 1
1001 JAMEEL 2 42000
1002 AHAMED 2 44000
1004 KANNAN 2 45000


Here all the rows in the emp tables will get dispaly but the salary column will list only if the depit is 2 and for rest of the deptid the salary column will be masked i.e. the value will not be shown.

To drop the policy:-

SQL> begin
dbms_rls.drop_policy
(
object_schema => 'MANZOOR',
object_name => 'EMP',
policy_name => 'COLMASKING'
);
END;
/

SQL - Basics

Structured Query Language


DDL - Data Definition Language
------------------------------
Create
alter
drop
rename
truncate


DML- Data Manipulation Language
-------------------------------
Insert
Update
Delete
Select

TCL - Transaction Control Language
----------------------------------
Commit
Rollback
savepoint

DCL - Data Control language
---------------------------
grant
revoke


How to Create tables:-

Syntax

create table table_name (col1 datatype, col2 datatype, ......, coln datatype);

; -> is called Terminatable symbol or execute sysmbol

Eg

SQL> create table emp (empid number, empname varchar2(30), age number, doj date, salary number(7,2));

Table created.


How to insert values in to tables

SQL> insert into emp values (1001,'Rajesh',28,'21-JUN-2010',38000.00);

1 row created.

Interative mode insert:-

SQL> insert into emp values (&empid , '&empname', &age,'&doj',&salary);
Enter value for empid: 1002
Enter value for empname: Jameel
Enter value for age: 29
Enter value for doj: 20-JAN-2003
Enter value for salary: 35000.00
old 1: insert into emp values (&empid , '&empname', &age,'&doj',&salary)
new 1: insert into emp values (1002 , 'Jameel', 29,'20-JAN-2003',35000.00)

1 row created.

SQL> /
Enter value for empid: 1003
Enter value for empname: Madhan
Enter value for age: 28
Enter value for doj: 30-NOV-2008
Enter value for salary: 25000
old 1: insert into emp values (&empid , '&empname', &age,'&doj',&salary)
new 1: insert into emp values (1003 , 'Madhan', 28,'30-NOV-2008',25000)

1 row created.


/ -> This sysmbol is used to execute the lastly executed statement.

To insert null values or to insert only specific column then

SQL> insert into emp values (1001,NULL,28,'21-JUN-2010',38000.00);

1 row created.

SQL> insert into emp (empid, empname) values (1002,'Kareem');

1 row created.


Selecting from Table:-

SQL> select * from emp;

EMPID EMPNAME AGE DOJ SALARY
---------- ------------------------------ ---------- --------- ----------
1001 Rajesh 28 21-JUN-10 38000
1002 Jameel 29 20-JAN-03 35000
1003 Madhan 28 30-NOV-08 25000
1001 28 21-JUN-10 38000
1002 Kareem

SQL> select * from emp where age > 28;

EMPID EMPNAME AGE DOJ SALARY
---------- ------------------------------ ---------- --------- ----------
1002 Jameel 29 20-JAN-03 35000

SQL> select * from emp where age >= 28;

EMPID EMPNAME AGE DOJ SALARY
---------- ------------------------------ ---------- --------- ----------
1001 Rajesh 28 21-JUN-10 38000
1002 Jameel 29 20-JAN-03 35000
1003 Madhan 28 30-NOV-08 25000
1001 28 21-JUN-10 38000

SQL> select * from emp where salary < 30000;

EMPID EMPNAME AGE DOJ SALARY
---------- ------------------------------ ---------- --------- ----------
1003 Madhan 28 30-NOV-08 25000


Relational operators-
<, >, <=, >=, <>, =

Eg:-

SQL> select empname from emp where salary > 30000;

EMPNAME
------------------------------
Rajesh
Jameel


Logical Operators
and, or, not

SQL> select empname from emp where salary > 30000 and age = 28;

EMPNAME
------------------------------
Rajesh

SQL> select empname from emp where salary > 30000 or age > 28;

EMPNAME
------------------------------
Jameel


Character operator

between, in, not in , like , not like

Eg:-

SQL> select * from emp where salary between 30000 and 35000;

EMPID EMPNAME AGE DOJ SALARY
---------- ------------------------------ ---------- --------- ----------
1002 Jameel 29 20-JAN-03 35000

SQL> select * from emp where empid in (1000,1001,1002);

EMPID EMPNAME AGE DOJ SALARY
---------- ------------------------------ ---------- --------- ----------
1002 Jameel 29 20-JAN-03 35000

SQL> select * from emp where empid not in (1000,1001,1002);

EMPID EMPNAME AGE DOJ SALARY
---------- ------------------------------ ---------- --------- ----------
1003 Madhan 28 30-NOV-08 25000

SQL> select * from emp where empname like '%a%';

EMPID EMPNAME AGE DOJ SALARY
---------- ------------------------------ ---------- --------- ----------
1002 Jameel 29 20-JAN-03 35000
1003 Madhan 28 30-NOV-08 25000


Aliase:-


SQL> select empname as name from emp;

NAME
------------------------------
Manzoor
Jameel
Madhan

Concatination:- (||)

SQL> select empname || ' is getting monthly salary of Rs.' || salary as Salary_Details from emp;

SALARY_DETAILS
--------------------------------------------
Jameel is getting monthly salary of Rs.35000
Madhan is getting monthly salary of Rs.25000


View Table structure:-

desc table_name or describe table_name

The symbol != and ^= Represents not equal to <>

like

% - > denotes many character
_ - > denotes one character

eg

like 'SA_'

SAB
SAJ

like 'SA_E'

SAVE
SAME

Sorting
order by - asc (Ascending) or desc (desending) - Default is asc

SQL> select empname, salary from emp order by salary;

EMPNAME SALARY
------------------------------ ----------
Madhan 25000
Jameel 35000
Rajesh 38000

3 rows selected.

SQL> select empname, salary from emp order by salary desc;

EMPNAME SALARY
------------------------------ ----------
Rajesh 38000
Jameel 35000
Madhan 25000

SQL> select * from emp order by salary, doj;

EMPID EMPNAME AGE DOJ SALARY
---------- ------------------------------ ---------- --------- ----------
1003 Madhan 28 30-NOV-08 25000
1002 Jameel 29 20-JAN-03 35000
1001 Rajesh 28 21-JUN-10 38000


Single Row Functions:-

1) Character Functions.
2) Number Functions.
3) Date Functions.
4) Conversion Functions.

Conversion Funcations(Convert a value from one data type to another)

NVL, NVL2, NULLIF, COALESCE, CASE, DECODE

NVL(exp1,exp2)
- Converts nullto actual value, i.e if the exp1 is null then it will return the
value specified in exp2.

SQL> select empid, nvl(empname,'Name Not Known') from emp;

EMPID NVL(EMPNAME,'NAMENOTKNOWN')
---------- ------------------------------
1001 Rajesh
1002 Jameel
1003 Madhan
1004 Name Not Known

NVL2(exp1,exp2,exp3)
- if exp1 is not null then it will return exp2 , if the exp1 is null then it will
return exp3.

SQL> select empid, nvl2(empname,empname,'Name not known') from emp;

EMPID NVL2(EMPNAME,EMPNAME,'NAMENOTK
---------- ------------------------------
1001 Rajesh
1002 Jameel
1003 Madhan
1004 Name not known

NULLIF(exp1,exp2)
- retunrs null if both exp1 and exp2 are same or it will returns the exp1 if they are not same.

Eg.
NULLIF (length(first_name),length(last_name))



COALESCE(exp1, exp2,exp3,.....expn)
- Retunrs the first non null expression in the expressions lists.

Eg.
COALESCE(commpct,salary,10)

if commpact is null not then it will return commpact elseif
commpct is null and salary is not null then it will return salary elseif
commpct and salary are null then it will return 10.

CASE

SQL> select empid, empname, salary,
case
when salary > 36000 then 'A Grade'
when salary > 34000 and salary < 36000 then 'B Grade'
else 'Other Grade'
end case
from emp
/

EMPID EMPNAME SALARY CASE
----- ------------------------------ ---------- -----------
1001 Rajesh 38000 A Grade
1002 Jameel 35000 B Grade
1003 Madhan 25000 Other Grade

SQL> select empid, empname, salary,
case
when salary between 36000 and 40000 then 'A Grade'
when salary between 34000 and 35999 then 'B Grade'
else 'Other Grade'
end case
from emp
/

EMPID EMPNAME SALARY CASE
----- ------------------------------ ---------- -----------
1001 Rajesh 38000 A Grade
1002 Jameel 35000 B Grade

DECODE(col1,search1,result1,search2,result2.... default);

Eg:
SQL> select empid,empname, salary,
decode(salary,38000,'A Grade',35000,'B Grade','Other Grade') from emp;

EMPID EMPNAME SALARY DECODE(SALA
---------- ------------------------------ ---------- -----------
1001 Rajesh 38000 A Grade
1002 Jameel 35000 B Grade
1003 Madhan 25000 Other Grade

Character Functions:-

lower, upper, initcap, concat, substr, length, instr, lpad, rpad, trim,replace


Eg:-

lower,upper and initcap
SQL> select lower(empname) ,upper(empname), initcap(empname) from emp;

LOWER(EMPNAME) UPPER(EMPNAME) INITCAP(EMPNAME)
------------------------------ ------------------------------ -------------------
rajesh RAJESH Rajesh
jameel JAMEEL Jameel
madhan MADHAN Madhan

Concat

SQL> select concat(empname,concat('is getting salary of Rs.',salary)) from emp;

CONCAT(EMPNAME,CONCAT('ISGETTINGSALARYOFRS.',SALARY))
-------------------------------------------------------------------------------
Rajeshis getting salary of Rs.38000
Jameelis getting salary of Rs.35000
Madhanis getting salary of Rs.25000

substr,length,instr

SQL> select substr(empname,1,3) , length(empname), instr(empname,'a') from emp;

SUB LENGTH(EMPNAME) INSTR(EMPNAME,'A')
--- --------------- ------------------
Raj 6 2
Jam 6 2
Mad 6 2

lpad,rpad,trim

SQL> select lpad(salary,10,'*'), rpad(salary,10,'*'), trim('H' from 'Hellow') from emp;

LPAD(SALAR RPAD(SALAR TRIM(
---------- ---------- -----
*****38000 38000***** ellow
*****35000 35000***** ellow
*****25000 25000***** ellow


replace

SQL> select replace(empname,'a','B') from emp;

REPLACE(EMPNAME,'A','B')
------------------------------
RBjesh
JBmeel
MBdhBn

Number Functions:-
round, trunc,mod

SQL> select round(45.46) from dual;

ROUND(45.46)
------------
45


SQL> select round(45.56) from dual;

ROUND(45.56)
------------
46


SQL> select trunc(45.96) from dual;

TRUNC(45.96)
------------
45

SQL> select mod(100,3) from dual;

MOD(100,3)
----------
1

1 row selected.

SQL> select mod(100,2) from dual;

MOD(100,2)
----------
0

Dual - Dual is a dummy table which is used to view the results from functinons and calculations.

Date Functions:-

Arithmetic with dates:-

SQL> select sysdate, sysdate+1, sysdate-1 from dual;

SYSDATE SYSDATE+1 SYSDATE-1
------------------ ------------------ ------------------
12-DEC 10 12:12:30 13-DEC 10 12:12:30 11-DEC 10 12:12:30

SQL> select sysdate,sysdate+10/24 , sysdate-10/24 from dual;

SYSDATE SYSDATE+10/24 SYSDATE-10/24
------------------ ------------------ ------------------
12-DEC 10 12:13:03 12-DEC 10 22:13:03 12-DEC 10 02:13:03


SQL> select doj, empname || 'is Working for ' ||
trunc(sysdate-doj) || ' Days ' ||
trunc((sysdate-doj)/7) || ' Weeks' ||
trunc((sysdate-doj)/30 ) || ' Months'||
trunc((sysdate-doj)/365) || ' Years'
from emp

21-JUN-10 Rajeshis Working for 174 Days 24 Weeks 5 Months 0Years
20-JAN-03 Jameelis Working for 2883Days 411 Weeks 96 Months 7Years
30-NOV-08 Madhanis Working for 742 Days 106 Weeks 24 Months 2Years

months_between -> No. of months between 2 dates
add_months -> Add calendar months to date
next_day -> next day of the specified date
last_day -> last day of the month
round -> round date
trunc -> truncate date

Data type conversions:-

to_char, to_number, to_date

Store Date Format

select
to_char(sysdate,'SCC') "Cetury",
to_char(sysdate,'yyyy') "YYYY",
to_char(sysdate,'yyy') "YYY",
to_char(sysdate,'yy') "YY",
to_char(sysdate,'y') "Y",
to_char(sysdate,'y,yyy') "Y,YYY",
to_char(sysdate,'syear') "Spelled year",
to_char(sysdate,'year') "Spelled Year",
to_char(sysdate,'BC') "Cen",
to_char(sysdate,'AD') "Cen",
to_char(sysdate, 'Q') "Quarter",
to_char(sysdate,'MM') "Month",
to_char(sysdate,'Month') "Month",
to_char(sysdate,'RM') "Roman",
to_char(sysdate,'WW') "Week",
to_char(sysdate,'W') "Week Day",
to_char(sysdate,'DDD') "Day 3Char",
to_char(sysdate,'DD') "Date",
to_char(sysdate,'D') "Day",
to_char(sysdate,'DAY') "Day" ,
to_char(sysdate, 'DY') "Day",
to_char(sysdate, 'J') "Julian Day",
to_char(sysdate,'AM PM hh hh12 hh24 mi ss ssss') "Time"
from dual


Cet ----- 21
YYYY ----- 2010
YYY ----- 010
YY ----- 10
Y ----- 0
Y,YYY ----- 2,010
Spelled year ----- twenty ten
Spelled Year ----- twenty ten
Ce ----- AD
Ce ----- AD
Q ----- 4
Mo ----- 12
Month ----- December
Roma ----- XII
We ----- 50
W ----- 2
Day ----- 346
Da ----- 12
D ----- 1
Day ----- SUNDAY
Day ----- SUN
Julian Day ----- 2455543
Time ----- PM PM 12 12 12 52 17 1717


SQL> select to_char(sysdate,'DDTHSP') from dual;

TO_CHAR(SYSDAT
--------------
TWELFTH

SQL> select to_char(sysdate,'DDSP') from dual;

TO_CHAR(SYSD
------------
TWELVE

SQL> select to_char(sysdate,'DDTH') from dual;

TO_C
----
12TH

Joins:-

Equijoin, Non Equijoin, Outer Join, Self Join

Equijoin ( Also called simple join or inner join)
-------------------------------------------------

EG:-

SQL> select e.empname , e.age, d.name from emp e, department d where e.dept = d.dept;

EMPNAME AGE NAME
------------------------------ ---------- ------------------------------
Rajesh 28 Purchase
Jameel 29 Sales
Madhan 28 IT
Kannan 29 Infra

SQL> select e.empname , e.age, d.name from emp e, department d where e.dept = d.dept and e.empname = 'Rajesh';

EMPNAME AGE NAME
------------------------------ ---------- ------------------------------
Rajesh 28 Purchase


Join more the 2 tables:-

For n number of table joins we need n-1 joins

Eg:-

select e.last_name, d.deptname, l.city from emp e, department d, city l where
e.deptid = d.deptid and d.location = l.locationid;


Non-Equijoin

EG:-

SQL> select empname, salary from emp;

EMPNAME SALARY
------------------------------ ----------
Rajesh 38000
Jameel 35000
Madhan 25000
Kannan 40000


SQL> select * from salgrade;

MIN MAX G
---------- ---------- -
50000 1000000 A
25000 49599 B
10000 24999 C

Eg

SQL> select e.empname, e.salary , s.grade from emp e, salgrade s where
e.salary between s.min and s.max;

EMPNAME SALARY G
------------------------------ ---------- -
Madhan 25000 B
Jameel 35000 B
Rajesh 38000 B
Kannan 40000 B

Outer Join:-

You use the outer join too see all the rows that do not meet join condition.
The outer join operator is the (+) sign.

EG:-

SQL> select empname, dept from emp;

EMPNAME DEPT
------------------------------ ----------
Rajesh 1
Jameel 2
Madhan 3
Kannan 4
Raja
Ramiz

SQL> select dept,name from department;

DEPT NAME
---------- ------------------------------
1 Purchase
2 Sales
3 IT
4 Infra
5 Software
6 Hardware


In the below statement the Hardware and software department are exists in the department table but there is no any employees pertaining to those department,
this can be accomplished by using the outer join.

SQL> select e.empname, d.name from emp e, department d where e.dept(+) = d.dept;

EMPNAME NAME
------------------------------ ------------------------------
Rajesh Purchase
Jameel Sales
Madhan IT
Kannan Infra
Hardware
Software


Also to list all the employees with and without department query as below

SQL> select e.empname, d.name from emp e, department d where e.dept = d.dept(+);

EMPNAME NAME
------------------------------ ------------------------------
Rajesh Purchase
Jameel Sales
Madhan IT
Kannan Infra
Ramiz
Raja

Self join :-

Joining a table to itself.

SQL> select * from emp;

EMPID EMPNAME AGE DOJ SALARY DEPT MANAGER_ID
------ ---------- ---------- --------- ---------- ---------- ----------
1001 Rajesh 28 21-JUN-10 38000 1 1002
1002 Jameel 29 20-JAN-03 35000 2 1003
1003 Madhan 28 30-NOV-08 25000 3 1004
1004 Kannan 29 03-MAY-06 40000 4 1005

Now we want to list the empname and his manager name, see that each employ is having a manger id and that manger is also an employ. So we can use the self join to accomplish this task.

SQL> select e.empname || ' is report to ' || m.empname from emp e, emp m where e.manager_id = m.empid;

E.EMPNAME||'ISREPORTTO'||M.EMPNAME
--------------------------------------------------------------------------
Rajesh is report to Jameel
Jameel is report to Madhan
Madhan is report to Kannan


Cross join

1) It is the cross product of 2 tables.
2) Same as Cartesian product between 2 tables.

select last_name, department_name from emp cross join departments


Natural Join

It was not possible to do a join without explicitly specifying the columns in the corresponding tables in prior release of the oracle server. In oracle 9i it is possible to let the join be completed automatically based on columns in 2 tables which have matching data type names, using the keywords NATURAL JOIN .

Note. The natural join can only be happen on columns having same name and data-type in both the tales. if columns name are same but data-type is different it will throw an error.

Eg:-
SQL> select name, department_name , location_id, city from departments natural join location;

SQL> select name, department_name, location_id, city from departments natural join location where department_id in (20,25,30);

Using Clause:-

If several columns have the same name and data-types then do no match the natural join clause , it can be modified with the using clause to specify the columns that should be used for an equijoin notes, use the using clause to match only one columns where more than one columns are matched. Do not use a table name or alias name in the referenced column.

Eg:-

Select l.city , d.department_name from location l join department d using (location_id) where location_id = 1400;

select e.empid, e.name , d.location_id from emp e join department d using (dept_id);

On clause:-

1) To specify arbitary conditions or specify colunms to join, the on clause random is used.
2) Separates the join condition from other search conditions.

Eg:-

select e.empid,e.lastname,e.dept_id, d.deptid, d.location from emp e join department d on (e.department_id = d.department_id);

self join using on

select e.lastname, m.lastname from emp e join emp m on (e.manager_id = m.employee_id)

Joining more than 2 tables using on:-

select e.emp_id, d.deptname, l.city from emp e join department d on (e.employee_id = d.department_id) join location l on (d.location_id = l.location_id);


Left outer join:-

SQL> select e.empname, d.name from emp e left outer join department d on ( e.dept = d.dept);

EMPNAME NAME
---------- ------------------------------
Rajesh Purchase
Jameel Sales
Madhan IT
Kannan Infra
Ramiz
Raja

Right outer join:-

SQL> select e.empname, d.name from emp e right outer join department d on ( e.dept = d.dept);

EMPNAME NAME
---------- ------------------------------
Rajesh Purchase
Jameel Sales
Madhan IT
Kannan Infra
Hardware
Software

Full outer join

SQL> select e.empname, d.name from emp e full outer join department d on ( e.dept = d.dept);

EMPNAME NAME
---------- ------------------------------
Rajesh Purchase
Jameel Sales
Madhan IT
Kannan Infra
Ramiz
Raja
Hardware
Software


Group by Functions:-

avg, count, max, min, sum, stddev, variance.

Note:- You cannot use the where caluse to restirct group, you can use the having clause to restrict group.

Eg:-

select dept_id, avg(salary) from emp having avg(salary) > 8000 group by dept_id;

(or)

select dept_id, avg(salary) from emp group by dept_id having avg(salary) > 8000;

Merge Statement

Syntax:-

Merge into table_name as table_alias
using (table | view | subquery) as alias
on (join condition)
when matched then
update set
col1 = col1_val, col2 = col2_val
when not matched then
insert (columns_list) values
(columns_value);

Eg:-


SQL> select * from copyof_emp;

EMPID EMPNAME AGE DOJ SALARY DEPT MANAGER_ID
---------- ---------- ---------- --------- ---------- ---------- ----------
1001 Rajesh 28 21-JUN-10 38000 1 1002
1002 Jameel 29 20-JAN-03 35000 2 1003

SQL> select * from emp;

EMPID EMPNAME AGE DOJ SALARY DEPT MANAGER_ID
---------- ---------- ---------- --------- ---------- ---------- ----------
1001 Rajesh 28 21-JUN-10 38000 1 1002
1002 Jameel 29 20-JAN-03 35000 2 1003
1003 Madhan 28 30-NOV-08 25000 3 1004
1004 Kannan 29 03-MAY-06 40000 4 1005

Merge into copyof_emp c
using emp e
on (c.empid = e.empid)
when matched then
update set
c.empname = e.empname,c.age=e.age,c.doj=e.doj,c.salary=e.salary,c.dept=e.dept,c.manager_id=e.manager_id
when not matched then
insert values
(e.empid,e.empname,e.age,e.doj,e.salary,e.dept,e.manager_id);



SQL> select * from copyof_emp;

EMPID EMPNAME AGE DOJ SALARY DEPT MANAGER_ID
---------- ---------- ---------- --------- ---------- ---------- ----------
1001 Rajesh 28 21-JUN-10 38000 1 1002
1002 Jameel 29 20-JAN-03 35000 2 1003
1003 Madhan 28 30-NOV-08 25000 3 1004
1004 Kannan 29 03-MAY-06 40000 4 1005

Query For Date Difference

select
EXTRACT(DAY FROM numtodsinterval(3.22,'DAY')) || ' days '
|| EXTRACT(HOUR FROM numtodsinterval(3.22,'DAY')) || ' hours '
|| EXTRACT(MINUTE FROM numtodsinterval(3.22,'DAY')) || ' minutes '
|| EXTRACT(SECOND FROM numtodsinterval(3.22,'DAY')) || ' seconds' from dual
/


Check the below for more functions....

http://www.java2s.com/Tutorial/Oracle/0200__SQL-Data-Types/Catalog0200__SQL-Data-Types.htm