Oracle9i SQL Reference Release 2 (9.2) Part Number A96540-02 |
|
|
View PDF |
The logging_clause
lets you specify whether creation of a database object will be logged in the redo log file (LOGGING
) or not (NOLOGGING
).
This clause also specifies whether subsequent Direct Loader (SQL*Loader) and direct-path INSERT
operations against the object are logged (LOGGING
) or not logged (NOLOGGING
).
You can specify the logging_clause
in the following statements:
CREATE
TABLE
and ALTER
TABLE
: for logging of the table, one of its partitions, a LOB segment, or the overflow segment of an index-organized table (see CREATE TABLE and ALTER TABLE).CREATE
INDEX
and ALTER
INDEX
: for logging of the index or one of its partitions (see CREATE INDEX and ALTER INDEX).CREATE
MATERIALIZED
VIEW
and ALTER
MATERIALIZED
VIEW
: for logging of the materialized view, one of its partitions, or a LOB segment (see CREATE MATERIALIZED VIEW and ALTER MATERIALIZED VIEW).CREATE
MATERIALIZED
VIEW
LOG
and ALTER
MATERIALIZED
VIEW
LOG
: for logging of the materialized view log or one of its partitions (see CREATE MATERIALIZED VIEW LOG and ALTER MATERIALIZED VIEW LOG.)CREATE
TABLESPACE
and ALTER
TABLESPACE
: to set or modify the default logging characteristics for all objects created in the tablespace (see CREATE TABLESPACE and ALTER TABLESPACE.)You can also specify LOGGING
or NOLOGGING
for the following operations:
logging_clause::=
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.
Specify LOGGING
if you want the creation of a database object, as well as subsequent inserts into the object, to be logged in the redo log file.
Specify NOLOGGING
if you do not want these operations to be logged.
CREATE
statement (and in subsequent ALTER
... ADD
PARTITION
statements), unless you specify the logging attribute in the PARTITION
description.If the object for which you are specifying the logging attributes resides in a database or tablespace in force logging mode, then Oracle 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 clauses that support LOGGING
. Only the following operations support the NOLOGGING
mode:
CREATE
TABLE
... AS
SELECT
CREATE
TABLE
... LOB_storage_clause
... LOB_parameters
... NOCACHE
| CACHE
READS
ALTER
TABLE
... LOB_storage_clause
... LOB_parameters
... NOCACHE
| CACHE
READS
(for specify logging of newly created LOB columns)ALTER
TABLE
... modify_LOB_storage_clause
... modify_LOB_parameters
... NOCACHE
| CACHE
READS
(to change logging of existing LOB columns)ALTER
TABLE
... MOVE
ALTER
TABLE
... [all partition operations that involve data movement]
ALTER
TABLE
... ADD
PARTITION
(hash partition only)ALTER
TABLE
... MERGE
PARTITIONS
ALTER
TABLE
... SPLIT
PARTITION
ALTER
TABLE
... MOVE
PARTITION
ALTER
TABLE
... MODIFY
PARTITION
... ADD SUBPARTITION
ALTER
TABLE
... MODIFY
PARTITION
... COALESCE
SUBPARTITION
ALTER
TABLE
... MODIFY
PARTITION
... REBUILD
UNUSABLE
INDEXES
CREATE
INDEX
ALTER
INDEX
... REBUILD
ALTER
INDEX
... REBUILD
PARTITION
ALTER
INDEX
... SPLIT
PARTITION
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:
CACHE
, then LOGGING
is used (because you cannot have CACHE
NOLOGGING
).NOCACHE
or CACHE
READS
, then the logging attribute defaults to the logging attribute of the tablespace in which it resides.NOLOGGING
does not apply to LOBs that are stored inline with row data. That is, 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.
See Also:
Oracle9i Database Concepts and Oracle9i Database Administrator's Guide for more information about logging and parallel DML |