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

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


Free Oracle App Server Tips


Oracle support

Oracle training

Oracle tuning


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 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
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.


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.