Sunday, November 01, 2009

Learn "NOLOGGING" with Tablespace "force logging" mode

After I learned, with...
If the database, or that tablespace, is in "force logging" mode, the nologging will not work.
Check Database is Archive Mode:
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 101
Next log sequence to archive 103
Current log sequence 103
Check REDO Size with "create table" logging:
SQL> @redo-new

OLD_VALUE
----------
1860

SQL> create table T_LOGGING tablespace TBS1 as select * from dba_objects;

Table created.

SQL> @redo-diff
old 1: select (value - &OLD_VALUE) OLD_VALUE
new 1: select (value - 1860) OLD_VALUE

OLD_VALUE
----------
8622128
Check REDO Size with "create table" nologging:
SQL> @redo-new

OLD_VALUE
----------
784

SQL> create table T_NOLOGGING nologging tablespace TBS1 as select * from dba_objects;

Table created.

SQL> @redo-diff
old 1: select (value - &OLD_VALUE) OLD_VALUE
new 1: select (value - 784) OLD_VALUE

OLD_VALUE
----------
105448
Check REDO Size with "create table" nologging on Tablespace "force logging" mode:
SQL> select tablespace_name, force_logging from dba_tablespaces where tablespace_name='TBS1';

TABLESPACE_NAME FOR
------------------------------ ---
TBS1 NO

SQL> alter tablespace TBS1 force logging;

Tablespace altered.

SQL> select tablespace_name, force_logging from dba_tablespaces where tablespace_name='TBS1';

TABLESPACE_NAME FOR
------------------------------ ---
TBS1 YES

SQL> @redo-new

OLD_VALUE
----------
788

SQL> create table T_NOLOGGING_F nologging tablespace TBS1 as select * from dba_objects;

Table created.

SQL> @redo-diff
old 1: select (value - &OLD_VALUE) OLD_VALUE
new 1: select (value - 788) OLD_VALUE

OLD_VALUE
----------
8640936
That's right, NOLOGGING isn't work on database or tablespace in "force logging" mode.

redo-new.sql:
column OLD_VALUE new_value OLD_VALUE
select value OLD_VALUE
from v$mystat, v$statname
where v$mystat.statistic# = v$statname.statistic#
and v$statname.name = 'redo size';
redo-diff.sql:
select (value - &OLD_VALUE) OLD_VALUE
from v$mystat, v$statname
where v$mystat.statistic# = v$statname.statistic#
and v$statname.name = 'redo size';

1 comment:

Azar Mohamed Shaj said...

I have seen this post right now only. When I aware about one of OTN users asked about force logging, meanwhile I have looked some reasonable point about it. I have cleared now from your post.

Thanks Sir.

Mohamed Azar.