当心 CREATE TABLE AS

当心 CREATE TABLE AS

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

    对 DBA而言，CREATE TABLE AS可谓是家常便饭，顺手拈来。殊不知该方式虽然简单，但疏忽也容易导致意想不到的问题。笔者前阵子就碰上了这样的事情。
   由于是对原表进行克隆，且数据存储在不同的表空间，因此毫不犹豫地使用了CREATE TABLEAS，结果在运行package时，error...

--1、非空约束遗失  
 
-->使用create table as 来创建对象  
scott@CNMMBO> create table tb_dept as select * fromdept 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 tableas时，索引是需要单独重建的。   
scott@CNMMBO> alter table tb_dept modify (deptno notnull);    
Table altered.  
 
scott@CNMMBO> drop tabletb_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) select50,'DEV' from dual;  
1 row created.  
 
scott@CNMMBO> commit;  
Commit complete.  
 
-->下面的查询可以看到新增记录50的loc为缺省值'BeiJing'  
scott@CNMMBO> select * from dept; 
 
    DEPTNODNAME         LOC  
---------- -------------- -------------  
       10ACCOUNTING    NEW YORK  
       20RESEARCH      DALLAS  
       30SALES         CHICAGO  
       40OPERATIONS    BOSTON  
       50DEV           BeiJing  
 
-->再次使用create table as来创建对象  
scott@CNMMBO> create table tb_dept as select * fromdept;  
Tablecreated.         
 
-->从下面可知，由于列loc存在default值，所以此时notnull约束被同时赋予   
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; 
 
    DEPTNODNAME         LOC  
---------- -------------- -------------  
       10ACCOUNTING    NEW YORK  
       20RESEARCH      DALLAS  
       30SALES         CHICAGO  
       40OPERATIONS    BOSTON  
       50DEV           BeiJing   
 
-->为新创建的表新增记录  
-->新增时发现尽管notnull约束生效，但原表上设定的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 (dnameunique);  
Table altered.  
 
scott@CNMMBO> create table tb_dept as select * fromdept;  
Table created.  
 
scott@CNMMBO> insert into tb_dept select60,'DEV','ShangHai' from dual;  
1 row created.  
 
scott@CNMMBO> commit;  
Commit complete.  
 
scott@CNMMBO> select * from tb_dept; 
 
    DEPTNODNAME         LOC  
---------- -------------- -------------  
       10ACCOUNTING    NEW YORK  
       20RESEARCH      DALLAS  
       30SALES         CHICAGO  
       40OPERATIONS    BOSTON  
       50DEV           BeiJing  
       60DEV           ShangHai  
 
 
-->有关check约束与外键约束不再演示  


--4、最彻底的解决办法   
scott@CNMMBO> selectdbms_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 1MAXEXTENTS 2147483645  
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1BUFFER_POOL DEFAULT)  
  TABLESPACE "GOEX_USERS_TBL" ENABLE,  
        UNIQUE ("DNAME")  
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255COMPUTE STATISTICS  
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1MAXEXTENTS 2147483645  
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1BUFFER_POOL DEFAULT)  
  TABLESPACE "GOEX_USERS_TBL" ENABLE  
   ) PCTFREE 10 PCTUSED 40INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING  
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1MAXEXTENTS 2147483645  
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1BUFFER_POOL DEFAULT)  
  TABLESPACE "GOEX_USERS_TBL" 
 
--5、演示环境   
scott@CNMMBO> select * from v$version whererownum<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: RobinsonCheng              
-->Blog:  http://blog.csdn.net/robinson_0612