Nologging and force logging

NOLOGGING can be used to prevent bulk operations from logging too much information to Oracle's Redo log files.

On the other hand, FORCE LOGGING can be used on tablespace or database level to force logging of changes to the redo. This may be required for sites that are mining log data, using Oracle Streams or using Data Guard (standby databases).

Nologging

NOLOGGING can be used to minimize the amount of redo generated by Oracle. Only the following operations can make use of nologging:

• SQL*Loader in direct mode
• INSERT /*+APPEND*/ ...
• CTAS
• ALTER TABLE statements (move/add/split/merge partitions)
• CREATE INDEX
• ALTER INDEX statements (move/add/split/merge partitions)

To create a table in NOLOGGING mode:

SQL> CREATE TABLE t1 (c1 NUMBER) NOLOGGING;
Table created.

To enable NOLOGGING for a table:

ALTER TABLE t1 NOLOGGING;
Table altered.

Force logging

A tablespace or the entire database is either in force logging or no force logging mode. To see which it is, run:

SQL> SELECT force_logging FROM v$database;
FOR
---
NO

or

SQL> SELECT tablespace_name, force_logging FROM dba_tablespaces;
TABLESPACE_NAME                FOR
------------------------------ ---
SYSTEM                         NO
...

To enable force logging:

SQL> ALTER DATABASE force logging;
Database altered.

SQL> ALTER TABLESPACE users FORCE LOGGING;
Tablespace altered.

To disable:

SQL> ALTER DATABASE no force logging;
Database altered.

SQL> ALTER TABLESPACE users NO FORCE LOGGING;
Tablespace altered.