再論執行oracle DDL語句要注意的問題

連結：http://lfree.itpub.net/post/4950/248772

> select * from dept;

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

> insert into dept (deptno) values (50);

1 row created.

> create table dept as select * from dept where rownum<=1 ;
create table dept as select * from dept where rownum<=1
*
ERROR at line 1:
ORA-00955: name is already used by an existing object

> rollback ;

Rollback complete.

> select * from dept;

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

可以看到即使建立表的語句失敗，後面執行了回滾，在ddl以前的dml語句的資料也已經插入了。