NOLOGGING

The important things to note with NOLOGGING operations are:
a) Some amount of redo will be generated, as a matter of fact. This redo is to protect the data dictionary. There is no avoiding this at all. It will be of a significantly less amount than before, but there will be some.

b) NOLOGGING does not prevent redo from being generated by all subsequent operations. In the above example, I did not create a table that is never logged. Only the single, individual operation of creating the table was not logged. All subsequent 'normal' operations such as INSERTs, UPDATEs, and DELETEs will be logged. Other special operations such as a direct path load using SQLLDR, or a direct path insert using the INSERT /*+ APPEND */ syntax will not be logged. In general however, the operations your application performs against this table will be logged.

c) After performing NOLOGGING operations in an ARCHIVELOG mode database, you must take a new baseline backup of the affected data files as soon as possible. This is in order to avoid losing subsequent changes to these objects due to media failure. We wouldn't actually lose the changes, as these are in the redo log. What we've actually lost is the data to apply the changes to.

There are two ways to use the NOLOGGING option. You have already seen one method, by embedding the keyword NOLOGGING in the SQL command itself at the appropriate location. The other method allows operations to be performed implicitly in a NOLOGGING mode. For example, I can alter an index to be NOLOGGING by default. This means that subsequent direct path loads and direct path inserts performed which affect this index, will not be logged (the index will not generate redo – other indexes and the table itself might but this index will not).The operations that may be performed in a NOLOGGING mode are:

a) Index creations and ALTERs (rebuilds).
b) Bulk INSERTs using a 'direct path insert' via the /*+ APPEND */ hint.
c) LOB operations (updates to large objects do not have to be logged).
d) Table creations via the CREATE TABLE AS SELECT.
e) Various ALTER TABLE operations such as MOVE and SPLIT.
f) TRUNCATE (but it does not need a NOLOGGING clause, as it is always in NOLOGGING mode).


It is even more deep then that. For example:

Table Mode Insert Mode ArchiveLog mode result
----------- ------------- ----------------- ----------
LOGGING APPEND ARCHIVE LOG redo generated
NOLOGGING APPEND ARCHIVE LOG no redo
LOGGING no append "" redo generated
NOLOGGING no append "" redo generated
LOGGING APPEND noarchive log mode no redo
NOLOGGING APPEND noarchive log mode no redo
LOGGING no append noarchive log mode redo generated
NOLOGGING no append noarchive log mode redo generated

Ref : Expert Oracle - Thomas Kyte