Misconception: NOLOGGING turns off redo logging

 

Jeremiah Wilton

jwilton@speakeasy.net

 

Many people believe that by setting a segment to NOLOGGING mode, no redo will be generated for DML performed against that segment.  In fact, only a limited number of operations can be performed NOLOGGING.  According to the Oracle 8i Concepts Guide, only the following operations may take advantage of the NOLOGGING feature:

 

·                     direct load (SQL*Loader)

·                     direct-load INSERT

·                     CREATE TABLE ... AS SELECT

·                     CREATE INDEX

·                     ALTER TABLE ... MOVE PARTITION

·                     ALTER TABLE ... SPLIT PARTITION

·                     ALTER INDEX ... SPLIT PARTITION

·                     ALTER INDEX ... REBUILD

·                     ALTER INDEX ... REBUILD PARTITION

·                     INSERT, UPDATE, and DELETE on LOBs in NOCACHE NOLOGGING mode stored out of line