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 partition
create table...as select
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:
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.
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.