??? 對 DBA 而言，CREATE TABLE AS 可謂是家常便飯，順手拈來。需不知該方式雖然簡單，但疏忽也容易導致意想不到的問題。筆者前陣子就碰上
了這樣的事情。由于是對原表進行克隆，且數據存儲在不同的表空間，因此毫不猶豫地使用了CREATE TABLE AS，結果在運行package時，error...

--1、非空約束遺失 -->使用create table as 來創建對象 scott@CNMMBO> create table tb_dept as select * from dept where 1=0; Table created. scott@CNMMBO> desc dept; Name Null? Type ----------------------------------------------------- -------- ------------------------------------ DEPTNO NOT NULL NUMBER(2) DNAME VARCHAR2(14) LOC VARCHAR2(13) scott@CNMMBO> desc tb_dept; Name Null? Type ----------------------------------------------------- -------- ------------------------------------ DEPTNO NUMBER(2) DNAME VARCHAR2(14) LOC VARCHAR2(13) -->從上面的desc可以看出新創建的表少了非空約束 -->下面手動為其增加非空約束，增加后與原來的表是一致的。當然使用create table as時，索引是需要單獨重建的。 scott@CNMMBO> alter table tb_dept modify (deptno not null); Table altered. scott@CNMMBO> drop table tb_dept; -->刪除剛剛穿件的表tb_dept Table dropped. --2、存在非空約束時default約束遺失 -->下面為表dept的loc列添加非空約束，且賦予default值 scott@CNMMBO> alter table dept modify (loc default 'BeiJing' not null); Table altered. -->為原始表新增一條記錄 scott@CNMMBO> insert into dept(deptno,dname) select 50,'DEV' from dual; 1 row created. scott@CNMMBO> commit; Commit complete. -->下面的查詢可以看到新增記錄50的loc為缺省值'BeiJing' scott@CNMMBO> select * from dept; DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON 50 DEV BeiJing -->再次使用create table as來創建對象 scott@CNMMBO> create table tb_dept as select * from dept; Table created. -->從下面可知，由于列loc存在default值，所以此時not null約束被同時賦予 scott@CNMMBO> desc tb_dept Name Null? Type ----------------------------------------------------- -------- ------------------------------------ DEPTNO NUMBER(2) DNAME VARCHAR2(14) LOC NOT NULL VARCHAR2(13) scott@CNMMBO> select * from tb_dept; DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON 50 DEV BeiJing -->為新創建的表新增記錄 -->新增時發現盡管not null約束生效，但原表上設定的default值不存在了 scott@CNMMBO> insert into tb_dept(deptno,dname) select 60,'HR' from dual; insert into tb_dept(deptno,dname) select 60,'HR' from dual * ERROR at line 1: ORA-01400: cannot insert NULL into ("SCOTT"."TB_DEPT"."LOC") scott@CNMMBO> drop table tb_dept; Table dropped. --3、唯一約束遺失 scott@CNMMBO> alter table dept modify (dname unique); Table altered. scott@CNMMBO> create table tb_dept as select * from dept; Table created. scott@CNMMBO> insert into tb_dept select 60,'DEV','ShangHai' from dual; 1 row created. scott@CNMMBO> commit; Commit complete. scott@CNMMBO> select * from tb_dept; DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON 50 DEV BeiJing 60 DEV ShangHai -->有關check約束與外鍵約束不再演示 --4、最徹底的解決辦法 scott@CNMMBO> select dbms_metadata.get_ddl('TABLE','DEPT') from dual; DBMS_METADATA.GET_DDL('TABLE','DEPT') -------------------------------------------------------------------------------- CREATE TABLE "SCOTT"."DEPT" ( "DEPTNO" NUMBER(2,0), "DNAME" VARCHAR2(14), "LOC" VARCHAR2(13) DEFAULT 'BeiJing' NOT NULL ENABLE, CONSTRAINT "PK_DEPT" PRIMARY KEY ("DEPTNO") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "GOEX_USERS_TBL" ENABLE, UNIQUE ("DNAME") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "GOEX_USERS_TBL" ENABLE ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "GOEX_USERS_TBL" --5、演示環境 scott@CNMMBO> select * from v$version where rownum<2; BANNER ---------------------------------------------------------------- Oracle Database 10g Release 10.2.0.3.0 - 64bit Production --6、演示結論 -->create table as 盡管會克隆表及數據，數據是完整的，但是結構部分僅僅是部分克隆 -->create table as 會使用表上的約束被遺失或出于非正常狀態 -->create table as 時，表上的索引、觸發器等不會被同時克隆 -->create table as 僅作測試使用，要得到完整的結構語句，還是使用dbms_metadata.get_ddl包 -->Author: Robinson Cheng -->Blog: http://blog.csdn.net/robinson_0612

更多參考：

啟用用戶進程跟蹤

父游標、子游標及共享游標

綁定變量及其優缺點

dbms_xplan之display_cursor函數的使用

dbms_xplan之display函數的使用

執行計劃中各字段各模塊描述

使用 EXPLAIN PLAN 獲取SQL語句執行計劃

啟用 AUTOTRACE 功能

函數使得索引列失效

Oracle 綁定變量窺探

當心 CREATE TABLE AS