Call for Oracle support & training (800) 766-1884
Free Oracle Tips

Home
Corporate Oracle Training
Custom Oracle Training
Oracle New Features Training
Advanced Oracle DBA Classes
Oracle Tuning Courses
Oracle Tips & Tricks
Oracle Training Links
Oracle Training Links
Oracle Training Links

We are top for USA Oracle Training Clients

 

Free Oracle Tips


 
HTML Text AOL

Free Oracle App Server Tips


 
HTML Text

Oracle support

Oracle training

Oracle tuning

Rednecks!

Remote Oracle

Custom Oracle Training

 

Donald K. Burleson

Oracle Tips

 

Watch out for the UNRECOVERABLE and NOLOGGING clause

 

Be very careful using UNRECOVERABLE clause (Oracle7) and the NOLOGGING clause (Oracle8) when performing CREATE INDEX or CREATE TABLE AS SELECT (CTAS) commands. 

 

The CTAS with NOLOGGING or UNRECOVERABLE will send the actual create statement to the redo logs (this information is needed in the data dictionary), but all rows loaded into the table during the operation are NOT sent to the redo logs.

 

With NOLOGGING in Oracle8, although you can set the NOLOGGING attribute

for a table, partition, index, or tablespace, NOLOGGING mode does not apply to every operation performed on the schema object for which you set the NOLOGGING attribute.

 

Only the following operations can make use of the NOLOGGING option:

 

    alter table...move partition

    alter table...split partition

    alter index...split partition

    alter index...rebuild

    alter index...rebuild partition

    create table...as select

    create index

    direct load with SQL*Loader

    direct load INSERT

   

 

Many Oracle professionals use NOLOGGING because the actions runs fast because the Oracle redo logs are bypassed.  However, this can be quite dangerous if you need to roll-forward through this time period during a database recovery.

 

It is not possible to roll forward through a point in time when an NOLOGGING operation has taken place.  This can be a CREATE INDEX NOLOGGING, CREATE TABLE AS SELECT NOLOGGING, or an NOLOGGING table load.

 

The NOLOGGING clause is a wonderful tool since it often halves run times, but you need to remember the danger.  For example, a common practice is to reorganize very large tables is to use CTAS:

 

Create table
   new_customer
tablespace
   new_ts
NOLOGGING
as
select * from customer;

Drop table customer;
Rename new_customer to customer;

 

However, you must be aware that a roll-forward through this operation is not possible, since there are no images in the archived redo logs for this operation.  Hence, you MUST take a full backup after performing any NOLOGGING operation.

 


Reader comments

I thought it would be useful to point out that using UNRECOVERABLE leaves the newly created table in LOGGING mode, however NOLOGGING leaves it in NOLOGGING mode which will prevent the table from being rolled forward after future changes are applied (assuming a backup is first taken of course). If logging is required on the table then it must be altered.

Regards.

Paul Hill
Langwith Consulting Services Ltd

 

 

 

 

 
 
 

Oracle performance tuning book

 

 

Oracle performance tuning software

 
Oracle performance tuning software
 
Oracle performance Tuning 10g reference poster
 
Oracle training in Linux commands
 
Oracle training Excel
 
 
 
 
 
email BC:


Copyright © 1996 -  2014 by Burleson Inc. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.