create table as引发的问题

转自：https://blog.csdn.net/leshami/article/details/7362156

1.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约束与外键约束不再演示

2.问题解决

通过dbms_metadata包的get_ddl过程进行获取完整的表结构

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"

3.结论

1. create table as时，尽管会克隆表及数据，数据是完整的，但是结构部分仅仅是部分克隆
2. create table as时，会使表上的约束被遗失或出于非正常状态
3. create table as时，表上的索引、触发器等不会被同时克隆
4. create table as 仅作测试使用，要得到完整的结构语句，还是使用dbms_metadata.get_ddl包