Force logging and nologging mode [Oracle]

A database is either in force logging or no force logging mode. Use v$database.force_logging to determine in which it is.

Yet to be finished ..

Nologging can be used to minimize the amount of redo generated.

Only the following statements can make use of nologging:

• alter table ... move partition
• alter table ... split partition
• alter table ... add partition (if hash partition)
• alter table ... merge partition
• alter table ... modify partition
  
  • add subpartition
  • coalesce subpartition
  • rebuild unusable indexes
• alter index ... split partition
• alter index ... rebuild
• alter index ... rebuild partition
• create table ... as select
• create index

Additionally, a direct load with SQL*Loader and a direct load insert can also make use of nologging. The direct load insert is a special form of the insert statement that uses the /*+ append */ hint.
Note: Even though direct path load reduces the generation of redo, it is not totally eliminated. That's because those inserts still generate undo which in turn generates redo.

That means that ordinary inserts, updates and deletes always generate redo, no matter if the underlying table or index is specifed with nologging or not.

If there is an index on the table, and an +append insert is made on the table, the indexes will produce redo. This can be circumvented by setting the index to unusable and altering the session's skip_unusable_indexes to true.

Nologging can be overriden at tablespace level using alter tablespace ... force logging.

Nologging has no effect if the database is in force logging mode (which can be controlled with alter database force [no] logging mode).