oracle不产生特定表的归档,归档模式下，能否实现对某个表的dml操作不产生重做日志？...

$ sqlplus " /as sysdba"

SQL*Plus: Release 8.1.7.0.0 - Production on Thu Dec 2 01:41:20 2004

(c) Copyright 2000 Oracle Corporation.  All rights reserved.

Connected to:

Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production

With the Partitioning option

JServer Release 8.1.7.4.0 - Production

SQL> archive log list;

Database log mode              No Archive Mode

Automatic archival             Disabled

Archive destination            /opt/oracle/db01/app/oracle/product/8.1.7/dbs/arc

h

Oldest online log sequence     642810

Current log sequence           642812

--logging模式

SQL> create table test (name varchar2(64));

Table created.

SQL> select usn,writes from v$rollstat;

USN     WRITES

---------- ----------

0       3496

1  149581088

2  653232300

3  703586798

4 1168146350

5  154556724

6  153673728

7  152731590

8  150812138

9 rows selected.

SQL> insert into test(name) select object_name from dba_objects;

23085 rows created.

SQL> select XIDUSN from v$transaction;

XIDUSN

----------

5

SQL> select usn,writes from v$rollstat;

USN     WRITES

---------- ----------

0       3496

1  149581088

2  653232300

3  703586798

4 1168146350

5  154613992

6  153673728

7  152731590

8  150812138

9 rows selected.

SQL> select 154613992-154556724 from dual;

154613992-154556724

-------------------

57268

SQL> insert /*+ append */into test(name) select object_name from all_objects;

19635 rows created.

SQL>  select XIDUSN from v$transaction;

XIDUSN

----------

5

SQL> select usn,writes from v$rollstat;

USN     WRITES

---------- ----------

0       3496

1  149581088

2  653232300

3  703586798

4 1168146350

5  154614640

6  153673728

7  152731590

8  150812138

9 rows selected.

SQL> select 154614640-154613992 from dual;

154614640-154613992

-------------------

648

--nologging模式

SQL> alter table test nologging ;

Table altered.

SQL>  select usn,writes from v$rollstat;

USN     WRITES

---------- ----------

0       3496

1        602

2        268

3       1174

4        108

5         54

6         54

7         54

8         54

9 rows selected.

SQL> insert into test(name) select object_name from dba_objects;

23085 rows created.

SQL> select XIDUSN from v$transaction;

XIDUSN

----------

5

SQL> select usn,writes from v$rollstat;

USN     WRITES

---------- ----------

0       3496

1        602

2        268

3       1174

4        108

5      60314

6         54

7         54

8         54

9 rows selected.

SQL> select 60314-54 from dual;

60314-54

----------

60260

SQL> insert /*+ append */into test(name) select object_name from all_objects;

23060 rows created.

SQL> select XIDUSN from v$transaction;

XIDUSN

----------

6

SQL> select usn,writes from v$rollstat;

USN     WRITES

---------- ----------

0       3496

1        602

2        268

3       1174

4        108

5      60314

6       1404

7         54

8         54

9 rows selected.

SQL> select 1404-54 from dual;

1404-54

----------

1350

结论:在oracle8i非归档模式下的append insert只是undo要比insert下的少，

但并不是完全没有undo 产生的,且不论是否是nologging方式,append insert都会产生较少的undo。

oracle8i归档模式:

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup  mount;

ORACLE instance started.

Total System Global Area  750117024 bytes

Fixed Size                    73888 bytes

Variable Size             508329984 bytes

Database Buffers          238493696 bytes

Redo Buffers                3219456 bytes

Database mounted.

SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

--nologging模式

SQL> select usn,writes from v$rollstat;

USN     WRITES

---------- ----------

0       3496

1        268

2       1174

3       1878

4         54

5         54

6         54

7         54

8         54

SQL> insert into test(name) select object_name from dba_objects;

23085 rows created.

SQL> select XIDUSN from v$transaction;

XIDUSN

----------

4

SQL> select usn,writes from v$rollstat;

USN     WRITES

---------- ----------

0       3496

1        268

2       1174

3       1878

4      60314

5         54

6         54

7         54

8         54

9 rows selected.

SQL> select  60314-54 from dual;

60314-54

----------

60260

SQL> insert /*+ append */into test(name) select object_name from all_objects;

23060 rows created.

SQL> select XIDUSN from v$transaction;

XIDUSN

----------

4

SQL> select usn,writes from v$rollstat;

USN     WRITES

---------- ----------

0       3496

1        268

2       1174

3       1878

4      61610

5         54

6         54

7         54

8         54

9 rows selected.

SQL> select 61610-60314 from dual;

61610-60314

-----------

1296