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:

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