oracle执行完语句在哪点提交,DDL语句的提交操作是在ddl完成之前还是完成之后？DDL是做两提交吗？...

以下是我做的测试，在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 owner='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:2 OP: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操作。

推测DDL会产生两次commit操作