再论执行oracle DDL语句要注意的问题

链接：http://lfree.itpub.net/post/4950/248772

scott@ORCL> select * from dept;

DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

scott@ORCL> insert into dept (deptno) values (50);

1 row created.

scott@ORCL> create table dept as select * from dept where rownumcreate table dept as select * from dept where rownum *
ERROR at line 1:
ORA-00955: name is already used by an existing object

scott@ORCL> rollback ;

Rollback complete.

scott@ORCL> select * from dept;

DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50

可以看到即使建立表的语句失败，后面执行了回滚，在ddl以前的dml语句的数据也已经插入了。