Skip Headers
Oracle® Database SQL Language Reference
11g Release 2 (11.2)

Part Number E10592-02
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

logging_clause

Purpose

The logging_clause lets you specify whether creation of a database object will be logged in the redo log file (LOGGING) or not (NOLOGGING).

You can specify the logging_clause in the following statements:

You can also specify LOGGING or NOLOGGING for the following operations:

Syntax

logging_clause::=

Description of logging_clause.gif follows
Description of the illustration logging_clause.gif

Semantics

This section describes the semantics of the logging_clause. For additional information, refer to the SQL statement in which you set or reset logging characteristics for a particular database object.

Note:

For LOB segments, with the NOLOGGING and FILESYSTEM_LIKE_LOGGING settings it is possible for data to be changed on disk during a backup operation, resulting in an inconsistent backup. To avoid this situation, ensure that changes to LOB segments are saved in the redo log file by setting LOGGING for LOB storage. Alternatively, change the database to FORCE LOGGING mode so that changes to all LOB segments are saved in the redo.

If the object for which you are specifying the logging attributes resides in a database or tablespace in force logging mode, then Oracle Database ignores any NOLOGGING setting until the database or tablespace is taken out of force logging mode.

If the database is run in ARCHIVELOG mode, then media recovery from a backup made before the LOGGING operation re-creates the object. However, media recovery from a backup made before the NOLOGGING operation does not re-create the object.

The size of a redo log generated for an operation in NOLOGGING mode is significantly smaller than the log generated in LOGGING mode.

In NOLOGGING mode, data is modified with minimal logging (to mark new extents INVALID and to record dictionary changes). When applied during media recovery, the extent invalidation records mark a range of blocks as logically corrupt, because the redo data is not fully logged. Therefore, if you cannot afford to lose the database object, then you should take a backup after the NOLOGGING operation.

NOLOGGING is supported in only a subset of the locations that support LOGGING. Only the following operations support the NOLOGGING mode:

DML:  

DDL:  

For objects other than LOBs, if you omit this clause, then the logging attribute of the object defaults to the logging attribute of the tablespace in which it resides.

For LOBs, if you omit this clause, then:

NOLOGGING does not apply to LOBs that are stored internally (in the table with row data). If you specify NOLOGGING for LOBs with values less than 4000 bytes and you have not disabled STORAGE IN ROW, then Oracle ignores the NOLOGGING specification and treats the LOB data the same as other table data.