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;
‘/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;
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’
FROM dba_tables
WHERE tablespace_name = ‘NOLOGGING_TS’
TABLE_NAME
LOGGING
—————————— ——-
ORACLE_TEST NO
—————————— ——-
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′
FROM dba_tables
WHERE table_name = ‘ORACLE_TEST2′
TABLE_NAME
LOGGING
—————————— ——-
ORACLE_TEST NOORACLE_TEST2 YES
—————————— ——-
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;
isim varchar2(10)
)
NOLOGGING;
insert a row
insert
into oracle_test3 values(‘oracle’);
commit;
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;
