iputb讨论链接:
http://www.itpub.net/showthread.php?threadid=242761
我们看以下测试:
SQL> archive log list Database log mode No Archive Mode Automatic archival Enabled Archive destination /opt/oracle/oradata/hsjf/archive Oldest online log sequence 156 Current log sequence 158 SQL> @redo SQL> 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 immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount; ORACLE instance started.
Total System Global Area 235999908 bytes Fixed Size 451236 bytes Variable Size 201326592 bytes Database Buffers 33554432 bytes Redo Buffers 667648 bytes Database mounted. SQL> alter database archivelog;
Database altered.
SQL> alter database open;
Database altered.
SQL> @redo SQL> 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 .
结论:
显然ctas的Nologging在archivelog模式下才会不记录redo,而在Noarchivelog模式下会产生同样多的redo 在归档模式下的CTAS nologging应该等价于非归档模式下ctas + insert append