Oracle : LOGGING / NOLOGGING

LOGGING is a keyword that used on creating the index, table or tablespace. If we use LOGGING when creating the object then DML operations on the objects are logged in redo log file. If we use NOLOGGING when creating the object, in some cases DML operations on the objects are not logged in redo log file. There are a lot of questions asked on the forums about LOGGING/NOLOGGING. Some of the questions as follows: What is the advantage of NOLOGGING? When should we use NOLOGGING ? If NOLOGGING is used on the tablespace level, can I use LOGGING on the tables which are in the NOLOGGING tablespace?

OK. Let us examine one by one.

let’s create a NOLOGGING tablespace.

CREATE TABLESPACE NOLOGGING_TS DATAFILE
‘/data_oracle/nologging_ts.dbf’ SIZE 1024M AUTOEXTEND OFF
NOLOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 10M
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;

Now, I will create a table in this tablespace. And I will not specify LOGGING or NOLOGGING when creating the table.

CREATE TABLE oracle_test(id NUMBER)
TABLESPACE nologging_ts;

Check the table LOGGING status with following query. You will see LOGGING=NO

SELECT table_name, logging
FROM dba_tables
WHERE tablespace_name = ‘NOLOGGING_TS’

TABLE_NAME                   LOGGING
—————————— ——-
ORACLE_TEST                         NO

Now, I will create table in NOLOGGING_TS tablespace with LOGGING clause.

CREATE TABLE oracle_test2 (id NUMBER) TABLESPACE nologging_ts LOGGING;

Rerun above query to see table LOGGING status. You will see LOGGING=YES

SELECT tablespace_name, logging
FROM dba_tables
WHERE table_name = ‘ORACLE_TEST2′

TABLE_NAME                                                                            LOGGING
——————————                                                                  ——-
ORACLE_TEST                         NOORACLE_TEST2                       YES

In that case, If you specify NOLOGGING on the tablespace level, then default logging status will be  NOLOGGING that the objects to be created within the tablespace.

If you create a table as NOLOGGING then you can change its logging status to LOGGING. Or the opposite might happen.

ALTER TABLE oracle_test LOGGING;

ALTER TABLE oracle_test2 NOLOGGING;

This situation applies in tablespace. If you create a tablespace as NOLOGGING then you can change its logging status to LOGGING.

ALTER TABLESPACE nologging_ts LOGGING;

After changing a tablespace logging status to LOGGING, NOLOGGING created objects will remain as NOLOGGING. If you create a new table in this tablespace then created table logging status will be LOGGING. In some cases, logging can continue on NOLOGGING. So what are these conditions?

Table mode	Insert mode	Archive Log Mode	Redo generation
LOGGING	APPEND	ARCHIVE LOG	REDO generated
NOLOGGING	APPEND	ARCHIVE LOG	no REDO
LOGGING	No APPEND	ARCHIVE LOG	REDO generated
NOLOGGING	No APPEND	ARCHIVE LOG	REDO generated
LOGGING	APPEND	NO ARCHIVE LOG	no REDO
NOLOGGING	APPEND	NO ARCHIVE LOG	no REDO
LOGGING	No APPEND	NO ARCHIVE LOG	REDO generated
NOLOGGING	No APPEND	NO ARCHIVE LOG	REDO generated

“Direct Path Load” operations on the NOLOGGING table are not generated redo. If “force logging” is enabled then always redo is generated. Even if table is LOGGING mode, the “Direct Path Load” operations doesn’t generate redo log  in NO ARCHIVE LOG. Even if the database in ARCHIVE LOG mode, redo is not generated on NOLOGGING table.

Direct Load Insert is a faster way of running an INSERT statement. It is particularly useful for inserting large numbers of rows. Direct Load Insert differs from Conventional Insert in that it bypasses the buffer cache.

Creating a table as NOLOGGING takes less time than to create LOGGING. Let a simple test.

SQL> set timing on

SQL> create table oracle_logging logging as select * from dba_tables;

Table created.

Elapsed: 00:00:01.20

Now let’s create it with nologging.

SQL> create table oracle_nologging nologging as select * from dba_tables;

Table created.

Elapsed: 00:00:00.60

And there is an important point. If you delete a row from NOLOGGING table then you can rollback. Baceause of  it is not depended on LOGGING/NOLOGGING. It is depended on UNDO segments. Let’s do a simple test.

Create a NOLOGGING table.

CREATE TABLE oracle_test3 (
isim varchar2(10)
)
NOLOGGING;

insert a row

insert into oracle_test3 values(‘oracle’);
commit;

delete a row

delete from oracle_test3;

After rollback, you can see deleted row. So, the rollback processis not associated with  LOGGING / NOLOGGING .

rollback;select * from oracle_test3;