OCP-042 LOGGING of the create tablespace ..

6. Which two statements regarding the LOGGING clause of the CREATE TABLESPACE. .. statement are

correct? (Choose two.)

A. This clause is not valid for a temporary or undo tablespace.

B. If the tablespace is in the NOLOGGING mode, no operation on the tablespace will generate redo.

C. The tablespace will be in the NOLOGGING mode by default, if not specified while creating a

tablespace.

D. The tablespacelevel

logging attribute can be overridden by logging specifications at the table, index,

materialized view, materialized view log, and partition levels.

Answer: AD

Logging_clause

Specify the default logging attributes of all tables, indexes, materialized views, materialized view logs, and partitions within the tablespace. 

LOGGING

 is the default. This clause is not valid for a temporary or undo tablespace.

The tablespace-level logging attribute can be overridden by logging specifications at the table, index, materialized view, materialized view log, and partition levels.

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:

• CREATE

  TABLE

   and 

  ALTER

  TABLE

  : for logging of the table, a table partition, a LOB segment, or the overflow segment of an index-organized table (seeCREATE TABLE and ALTER TABLE).

• CREATE

  INDEX

   and 

  ALTER

  INDEX

  : for logging of the index or an index partition (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:

• Rebuilding an index (using 

  CREATE

  INDEX

   ... 

  REBUILD

  )
• Moving a table (using 

  ALTER

  TABLE

   ... 

  MOVE

  )

Syntax

logging_clause::=

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.

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.

• For a nonpartitioned object, the value specified for this clause is the actual physical attribute of the segment associated with the object.
• For partitioned objects, the value specified for this clause is the default physical attribute of the segments associated with all partitions specified in the 

  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 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:  

• Direct-path 

  INSERT

   (serial or parallel) resulting either from an 

  INSERT

   or a 

  MERGE

   statement. 

  NOLOGGING

   is not applicable to any 

  UPDATE

   operations resulting from the 

  MERGE

   statement.
• Direct Loader (SQL*Loader)

DDL:  

• CREATE

  TABLE

   ... 

  AS

  SELECT

• CREATE

  TABLE

   ... 

  LOB_storage_clause

   ... 

  LOB_parameters

   ... 

  NOCACHE

   | 

  CACHE

  READS

• ALTER

  TABLE

   ... 

  LOB_storage_clause

   ... 

  LOB_parameters

   ... 

  NOCACHE

   | 

  CACHE

  READS

   (to 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

• CREATE

  INDEX

• ALTER

  INDEX

   ... 

  REBUILD

• ALTER

  INDEX

   ... 

  REBUILD

  [SUB]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:

• If you specify 

  CACHE

  , then 

  LOGGING

   is used (because you cannot have 

  CACHE

  NOLOGGING

  ).
• If you specify 

  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.

FORCE LOGGING

Use this clause to put the tablespace into 

FORCE

LOGGING

 mode. Oracle Database will log all changes to all objects in the tablespace except changes to temporary segments, overriding any 

NOLOGGING

 setting for individual objects. The database must be open and in 

READ

WRITE

 mode.

This setting does not exclude the 

NOLOGGING

 attribute. That is, you can specify both 

FORCE

LOGGING

 and 

NOLOGGING

. In this case, 

NOLOGGING

 is the default logging mode for objects subsequently created in the tablespace, but the database ignores this default as long as the tablespace or the database is in 

FORCE

LOGGING

mode. If you subsequently take the tablespace out of 

FORCE

LOGGING

 mode, then the 

NOLOGGING

 default is once again enforced.

Note:

FORCE

LOGGING

 mode can have performance effects. Please refer to  Oracle Database Administrator's Guide for information on when to use this setting.

Restriction on Forced Logging You cannot specify 

FORCE

LOGGING

 for an undo or temporary tablespace.