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

本文地址：http://www.45fan.com/a/question/71134.html