What is LOGGING,NOLOGGING and FORCE LOGGING

Nologging

NOLOGGING can be used to minimize the amount of redo generated by Oracle. NOLOGGING affect the recoverability.

Only the following operations can make use of nologging:


1.SQL*Loader in direct mode the +append hint

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).

2.CTAS (create table as select)
 

3.ALTER TABLE statements (move/add/split/merge/modify partitions) 

• 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 ... rebuild
• alter index ... rebuild partition
• create index

 4.ALTER INDEX statements (move/add/split/merge partitions) 
5.Check any datafile or tablespace contains BEGIN backup mode 
6.INSERT, UPDATE, and DELETE on LOBs in NOCACHE NOLOGGING mode stored out of line

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

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). oracle introduced the ALTER DATABASE FORCE LOGGING command in Oracle 9i R2.

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.