Oracle FAQ

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.

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;

Quick Links:
Have a Oracle Question
Do you have an Oracle Question?

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

Best Regards,
Oracle Database, SQL, Application, Programming Tips

All the site contents are Copyright © and the content authors. All rights reserved.
All product names are trademarks of their respective companies.
The site is not affiliated with or endorsed by any company listed at this site.
Every effort is made to ensure the content integrity.  Information used on this site is at your own risk.
 The content on this site may not be reproduced or redistributed without the express written permission of or the content authors.