How to find the version of each oracle product installed?

$ORACLE_HOME/orainst/inspdver utility provides a list of all the oracle products installed on the server with their verion numbers

Identifying culprit rows when enabling of a constraint fails

When you need to upload huge amount of data, everybody says, that it is better to disable the constraints to give better performance. But, what if afterwards enabling of constraints fail due to bad data. You can find the culprit records by using EXCEPTIONS clause of ALTER TABLE statement. For ex.
ALTER TABLE test ENABLE CONSTRAINT pk_test exceptions into exceptions.
where exceptions table can be created by running $ORACLE_HOME/rdbms/admin/utlexcpt.sql script.

Simpler way of finding plan and statistics of a query

Executing EXPLAIN PLAN and then selecting from plan_table is one way to get the execution plan of a sql statement. But for this you need syntax of both statements and patience to type all that stuff for each statement. Instead SQL*PLUS offers a nifty command to enable and disable the display of execution plan of each statement executed in that session. In addition to this it can display the statistics for each statement.

SET AUTOT[RACE] {OFF | ON | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]
thus SET AUTOTRACE ON will do the whole work for you

How to trap errors from sqlplus in NT bat scripts

There is a variable ERRORLEVEL in NT. When you exits from sqlplus with a constant and check this variable in NT script, you can trap the error . For ex. IF ERRORLEVEL 1 PLUS33 will startup the sqlplus if youspecified EXIT 1 to get out from the sqlplus.

How to find the datafile which contains a particular table?

If you query DBA_EXTENTS based on segment_name equal to your table name you will see that the column FILE_ID will show you the database file where each extent is located. You can then use FILE_ID to query DBA_DATA_FILES to find the name of the datafile. Or you could do it all in one query using a join.

How to measure less than a second of time interval?

It is possible to measure time interval of upto 1/100th of a second inoracle. DBMS_UTILITY.GET_TIME function returns a number which increments every 100th of second. Keep in mind that this number can be negative as well. Thus it can only be used for measuring time intervals. Also in sys.v_$timer table the column hsecs contains a number which also incre,emts every 100th of a second. This number can also be negative

How to use date index while using date ranges

It becomes tricky to use an index on date columns for date ranges beacause as soon as you use trunc(), oops! there oes you index. So here is a way to use your index on date column for date ranges...
where datefield >= to_date(to_char(date1,’DD-MON-YYYY’)||'00:00:00','DD-MON-YYYYHH24:MI:SS')
and datefield <= to_date(to_char(date2,’DD-MON-YYYY’)||'23:59:59','DD-MON-YYYYHH24:MI:SS')

How to roll back sequences?

Use a negative increment and select from the sequence once, then reset the increment back to its correct setting. For example, this should reset the qcs_ranum sequence back 100:
alter sequence qcs_ranum increment by -100;
select qcs_ranum.nextval from dual;
alter sequence qcs_ranum increment by 1;

Have a Oracle Question
Do you have an Oracle Question?

Oracle Books
Oracle Certification, Database Administration, SQL, Application, Programming Reference Books

Oracle Application
Oracle Application Hints and Tips

Oracle Home
Oracle Database, SQL, Application, Programming Tips