Friday, August 20, 2010

Tablespace NOLOGGING and objects

What happens with a NOLOGGING tablespace ? Will all the tables ( segments) created in that tablespace will not be logged by default?

By default the Tables (created in this Tablespace) will inherit the option NOLOGGING. It can be overridden at the Table level if you specify the option LOGGING when you create it.
So you can have Tables with the option LOGGING in a Tablespace with the option NOLOGGING.

The only reason to create a tablespace with nologging, or to later set the nologging attribute on a tablespace, is to change the default of all objects (segments) created in that tablespace from that point forward. It has no effect at all on segments that already exist in the tablespace.
That's it. It has no effect on how long it will take the tablespace to be created, or recoverability of that tablespace.

At the table/index level, it will only have an effect for direct load operations, such as insert /*+ append */, CTAS, or alter table move on a table, or index creation or rebuild.

Besides this, you have also the FORCE LOGGING /NO FORCE LOGGING mode.
The FORCE LOGGING / NO FORCE LOGGING is used to control at the Database or Tablespace level the Logging mode. By default the Database and Tablespaces are in NO FORCE LOGGING.
 The FORCE LOGGING mode always overrides the NOLOGGING option at the Tablespace or Table level.

So if you have the Database in FORCE LOGGING mode, your Tables will generates Redo logs even if these Tables are created with the option NOLOGGING.

It can be useful in a Data Guard so as to enforce Redo log generation to maintain the Standby Databases.

No comments: