The Oracle (tm) Users' Co-Operative FAQ
I have created a table with the nologging option, but any insert, update, or delete still generates redo log , why?
| Author's name: Mark J. Bobak Author's Email: mark@bobak.net |
Date written: 20th June 2001 Oracle version(s): 8.0, 8i |
| After creating a table w/ the nologging option, my DML still generates redo log activity. Why? I thought the point of nologging was to disable redo generation. |
First, it must be understood that redo generation is at the very heart of Oracle's ability to recover from virually any media failure. For that reason, the ability to disable it only exists for a small subset of commands. They are all related to serial and parallel direct-path loads.
What operations allow NOLOGGING?
DML statements such as insert, update,
and delete will always generate redo.
However, the nologging option may be utilized for
the following SQL statements (from the Oracle 8i concepts manual,
chapter 22):
- direct load (SQL*Loader)
- direct load INSERT (using APPEND hint)
- 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
For the statements listed above, undo and redo logging can be almost entirely disabled. New extents are marked invalid, and data dictionary changes are still logged. Note that a table or index with the nologging attribute (which can be seen in the LOGGING column of the DBA_TABLES or DBA_INDEXES view) will default to nologging when one of the above statements is executed.
What is the recoverability of objects created as NOLOGGING?
Since nologging disables writing redo log entries,
there is no way for Oracle to recover data related to nologging
operations. In the case of a media failure subsequent to a
nologging operation, Oracle will apply the redo log
transactions, but, when it reaches the transactions related to
the nologging operation, it will only be able to
apply the extent invalidation records, since that is all that was
recorded. Any subsequent attempt to access data in those
extents will result in an ORA-26040 "Data block was loaded
using the NOLOGGING option". The only resolution to
the error is to drop and recreate the object. Note that
this risk only exists until the next successful backup.
Backups taken after the completion of the nologging
operation will provide complete recovery.
Further reading: Using Oracle7 UNRECOVERABLE and Oracle8 NOLOGGING option (MetaLink Registration required)
This question is also addressed by the following document(s):
| Author | Title/URL | Suggested by | Referee's comments |
| Howard Rogers | What does the "nologging" operation do for me ? | Jonathan Lewis | A short pdf file with an intelligent discussion of
the issues. You will need Adobe Acrobat to read this
document. NOTE - The referenced site was closed down by Oracle Australia. in May 2002. This link has been maintained in case Howards gets permission to re-open the site. |
.
