create table as 产生的问题

 

 

1. --1、非空约束遗失  
2. -->使用create table as 来创建对象  
3. scott@CNMMBO> create table tb_dept as select * from dept where 1=0;  
4.   
5. Table created.  
6.   
7. scott@CNMMBO> desc dept;  
8.  Name                                                  Null?    Type  
9.  ----------------------------------------------------- -------- ------------------------------------  
10.  DEPTNO                                                NOT NULL NUMBER(2)  
11.  DNAME                                                          VARCHAR2(14)  
12.  LOC                                                            VARCHAR2(13)  
13.   
14. scott@CNMMBO> desc tb_dept;  
15.  Name                                                  Null?    Type  
16.  ----------------------------------------------------- -------- ------------------------------------  
17.  DEPTNO                                                         NUMBER(2)  
18.  DNAME                                                          VARCHAR2(14)  
19.  LOC                                                            VARCHAR2(13)  
20.   
21. -->从上面的desc可以看出新创建的表少了非空约束  
22. -->下面手动为其增加非空约束，增加后与原来的表是一致的。当然使用create table as时，索引是需要单独重建的。  
23. scott@CNMMBO> alter table tb_dept modify (deptno not null);    
24.   
25. Table altered.  
26.   
27. scott@CNMMBO> drop table tb_dept;    -->删除刚刚穿件的表tb_dept  
28.   
29. Table dropped.  
30.   
31. --2、存在非空约束时default约束遗失  
32. -->下面为表dept的loc列添加非空约束，且赋予default值  
33. scott@CNMMBO> alter table dept modify (loc default 'BeiJing' not null);  
34.   
35. Table altered.  
36.   
37. -->为原始表新增一条记录  
38. scott@CNMMBO> insert into dept(deptno,dname) select 50,'DEV' from dual;  
39.   
40. 1 row created.  
41.   
42. scott@CNMMBO> commit;  
43.   
44. Commit complete.  
45.   
46. -->下面的查询可以看到新增记录50的loc为缺省值'BeiJing'  
47. scott@CNMMBO> select * from dept;  
48.   
49.     DEPTNO DNAME          LOC  
50. ---------- -------------- -------------  
51.         10 ACCOUNTING     NEW YORK  
52.         20 RESEARCH       DALLAS  
53.         30 SALES          CHICAGO  
54.         40 OPERATIONS     BOSTON  
55.         50 DEV            BeiJing  
56.   
57. -->再次使用create table as来创建对象  
58. scott@CNMMBO> create table tb_dept as select * from dept;  
59.   
60. Table created.          
61.   
62. -->从下面可知，由于列loc存在default值，所以此时not null约束被同时赋予  
63. scott@CNMMBO> desc tb_dept  
64.  Name                                                  Null?    Type  
65.  ----------------------------------------------------- -------- ------------------------------------  
66.  DEPTNO                                                         NUMBER(2)  
67.  DNAME                                                          VARCHAR2(14)  
68.  LOC                                                   NOT NULL VARCHAR2(13)  
69.    
70. scott@CNMMBO> select * from tb_dept;  
71.   
72.     DEPTNO DNAME          LOC  
73. ---------- -------------- -------------  
74.         10 ACCOUNTING     NEW YORK  
75.         20 RESEARCH       DALLAS  
76.         30 SALES          CHICAGO  
77.         40 OPERATIONS     BOSTON  
78.         50 DEV            BeiJing   
79.   
80. -->为新创建的表新增记录  
81. -->新增时发现尽管not null约束生效，但原表上设定的default值不存在了  
82. scott@CNMMBO> insert into tb_dept(deptno,dname) select 60,'HR' from dual;  
83. insert into tb_dept(deptno,dname) select 60,'HR' from dual  
84. *  
85. ERROR at line 1:  
86. ORA-01400: cannot insert NULL into ("SCOTT"."TB_DEPT"."LOC")  
87.   
88. scott@CNMMBO> drop table tb_dept;  
89.   
90. Table dropped.  
91.  
92.   
93. --4、最彻底的解决办法  
94. scott@CNMMBO> select dbms_metadata.get_ddl('TABLE','DEPT') from dual;  
95.   
96. DBMS_METADATA.GET_DDL('TABLE','DEPT')  
97. --------------------------------------------------------------------------------  
98.   
99.   CREATE TABLE "SCOTT"."DEPT"  
100.    (    "DEPTNO" NUMBER(2,0),  
101.         "DNAME" VARCHAR2(14),  
102.         "LOC" VARCHAR2(13) DEFAULT 'BeiJing' NOT NULL ENABLE,  
103.          CONSTRAINT "PK_DEPT" PRIMARY KEY ("DEPTNO")  
104.   USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255  
105.   STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645  
106.   PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)  
107.   TABLESPACE "GOEX_USERS_TBL"  ENABLE,  
108.          UNIQUE ("DNAME")  
109.   USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS  
110.   STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645  
111.   PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)  
112.   TABLESPACE "GOEX_USERS_TBL"  ENABLE  
113.    ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING  
114.   STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645  
115.   PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)  
116.   TABLESPACE "GOEX_USERS_TBL"  
117.   
118. --5、演示环境  
119. scott@CNMMBO> select * from v$version where rownum<2;  
120.   
121. BANNER  
122. ----------------------------------------------------------------  
123. Oracle Database 10g Release 10.2.0.3.0 - 64bit Production  
124.   
125.  

 --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