Oracle Nologging & Logging Option

Mehmet Salih Deveci

3 years ago

I will explain Oracle Nologging & Logging in this post.

Oracle Nologging

When you insert any record to database, redolog and undo blocks are created in the logging mode. Logging mode slow down the inserts and index creation. So If you want to improve the Insert and Index creation performance, then you can use the Nologging option.

Nologging Advantages

Advantages of Nologging mode are as follows.

Best performance for Large insert operations
Best performance for the build times of large indexes
Best performance for the build times of large tables (CTAS)
The amount of redo IO and result in further reducing the number of archive log files

You can use the NOLOGGING in the following operations.

Create Table As Select (CTAS)
ALTER TABLE statements (add/move/merge/split partitions)
INSERT /*+APPEND*/
CREATE INDEX
ALTER INDEX statements (add/move/merge/split partitions)

Nologging mode is not safe, so you must take a backup, both before and after all nologging operations If your operation is critical.

You can insert into tables with nologging by using the APPEND hint, redo will be bypassed with APPEND hint.

Nologging clause can be used as follows.

INSERT /*+ APPEND NOLOGGING */ INTO TABLE_NAME;

Create table table_name NOLOGGING as  select * from other_table_name;

create index index_name . . . nologging;

alter table table_name nologging;

Nologging Tablespace & Database

You can enable the nologging in the database as follows.

SQL> ALTER DATABASE {NO} FORCE LOGGING;
SQL> select FORCE_LOGGING from v$database;

FORCE_LOGGING
---------------------------------------
NO

You can enable the nologging option for the any tablespace as follows.

SQL> ALTER TABLESPACE USERS {NO} FORCE LOGGING;
SQL> SELECT tablespace_name, force_logging FROM dba_tablespaces;

TABLESPACE_NAME                FOR
------------------------------ ---
USERS                         YES

Do you want to learn Oracle Database for Beginners, then Click and read the following articles.

Oracle Database Tutorials for Beginners ( Junior Oracle DBA )