CTAS、Nologging以及数据库运行模式

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