Oracle Nologging & Logging Option

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 )

 

 408 views last month,  3 views today

About Mehmet Salih Deveci

I am Founder of SysDBASoft IT and IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience.I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource.I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks.I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients.If you need Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS Consultancy and Training you can send my email adress [email protected].-                                                                                                                                                                                                                                                 -Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  [email protected] a mail atabilirsiniz.

Leave a Reply