Nologging and force logging examples

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[edit]

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[edit]

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.