IPUTB discussion link:
http://www.itpub.net/showthread.php?threadid=242761
We look at the following test:
SQL> archive log listDatabase log mode No Archive ModeAutomatic archival EnabledArchive destination / opt / oracle / oradata / hsjf / archiveOldest online log sequence 156Current log sequence 158SQL> @redoSQL> select * from redo_size;
Value ---------- 80892
SQL> CREATE TABLE TEST NOLOGGING AS SELECT * FROM DBA_OBJECTS;
Table created.
SQL> SELECT * from redo_size;
Value ---------- 118024
SQL> Drop Table Test;
Table Dropped.
SQL> SELECT * from redo_size;
Value ---------- 139732
SQL> CREATE TABLE TEST AS SELECT * FROM DBA_OBJECTS;
Table created.
SQL> SELECT * from redo_size;
Value ---------- 176864
SQL> SELECT (176864 -139732) Redo2, (118024 -80892) redo1 from dual
Redo2 redo1 ---------- ---------- 37132 37132
SQL> Drop Table Test;
Table Dropped.
SQL> Shutdown ImmediateDatabase Closed.Database DISMOUNTED.Oracle Instance Shut Down.sql> Startup Mount; Oracle Instance Started.
Total System Global Area 235999908 bytesFixed Size 451236 bytesVariable Size 201326592 bytesDatabase Buffers 33554432 bytesRedo Buffers 667648 bytesDatabase mounted.SQL> alter database archivelog;
Database altered.
SQL> ALTABASE OPEN;
Database altered.
SQL> @redosql> select * from redo_size;
Value ---------- 35768
SQL> CREATE TABLE TEST NOLOGGING AS SELECT * FROM DBA_OBJECTS;
Table created.
SQL> SELECT * from redo_size;
Value ---------- 73860
SQL> Drop Table Test;
Table Dropped.
SQL> SELECT * from redo_size;
Value ---------- 95596
SQL> CREATE TABLE TEST AS SELECT * FROM DBA_OBJECTS; TABLE CREATED.
SQL> SELECT * from redo_size;
Value ---------- 1215092
SQL> SELECT (1215092 -95596) REDO2, (73860 -35768) from dual
Redo2 (73860-35768) ---------- ------------- 1119496 38092.
in conclusion:
Obviously the CTAS's NOLOGGING will not record REDO in ArchiveLog mode, and the same multi-redo in NOARCHIVELOG mode will produce CTAS NOLogging in archiving mode to equivalent to CTAS INSERT APPEND in non-archive mode.
