为何DDL会产生两次commit操作

以下是我做的测试，在ddl完成之前 就提交了。

1.、会话1上的操作
SQL> select * from t2;
no rows selected
SQL> insert into t2 values(1,'gyj1');
1 row created.
SQL> drop table t200;
drop table t200
*
ERROR at line 1:

ORA-00942: table or view does not exist

2、会话2上的操作
SQL> select * from t2;
ID NAME
---------- ----------
1 gyj1

在会话2能看到会话1中插入数据。

3、继续实验
drop table t2;
select current_scn from v$database;
create table t2 (id int,name varchar2(10));
select current_scn from v$database;

SQL> select current_scn from v$database;
create table t2 (id int,name varchar2(10));
select current_scn from v$database;
CURRENT_SCN
-----------
2030192
SQL>
Table created.
SQL>
CURRENT_SCN
-----------
2030199
SQL> select object_id from dba_objects where object_name='T2' and wner='GYJ';
OBJECT_ID
----------
76443

alter system dump logfile '/u01/app/oracle/oradata/ocp/redo01.log' scn min 2030192 scn max 2030199;
从日志中转存的部分内容：
REDO RECORD - Thread:1 RBA: 0x0000b5.000000fb.0058 LEN: 0x0060 VLD: 0x01
SCN: 0x0000.001efa71 SUBSCN: 1 12/06/2012 07:33:07
CHANGE #1 TYP:0 CLS:33 AFN:3 DBA:0x00c00100 OBJ:4294967295 SCN:0x0000.001efa70 SEQ:2OP:5.4 ENC:0 RBL:0
ktucm redo: slt: 0x001d sqn: 0x000004db srt: 0 sta: 9 flg: 0x2 ktucf redo: uba: 0x00c0028d.01a2.03 ext: 2 spc: 7832 fbi: 0

REDO RECORD - Thread:1 RBA: 0x0000b5.00000100.01bc LEN: 0x0170 VLD: 0x01
SCN: 0x0000.001efa72 SUBSCN: 1 12/06/2012 07:33:07
CHANGE #1 MEDIA RECOVERY MARKER SCN:0x0000.00000000 SEQ:0 OP:24.1 ENC:0
REDO RECORD - Thread:1 RBA: 0x0000b5.00000102.0010 LEN: 0x00dc VLD: 0x05
SCN: 0x0000.001efa75 SUBSCN: 1 12/06/2012 07:33:07
(LWN RBA: 0x0000b5.00000102.0010 LEN: 0001 NST: 0001 SCN: 0x0000.001efa73)
CHANGE #1 TYP:0 CLS:33 AFN:3 DBA:0x00c00100 OBJ:4294967295 SCN:0x0000.001efa71 SEQ:1 OP:5.4 ENC:0 RBL:0

在这两个scn之间有两个OP=5.4的操作，即commit操作。

查看本栏目更多精彩内容：http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/