当心 CREATE TABLE AS_数据库技术_Linux公社-Linux系统门户网站
你好，游客
当心 CREATE TABLE AS
来源：CSDN&
作者：Robinson Cheng
对 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&&Name& & & & & & & & & & & & & & & & & & & & & & & & & Null?& & Type&----------------------------------------------------- -------- ------------------------------------&DEPTNO& & & & & & & & & & & & & & & & & & & & & & & & NOT NULL NUMBER(2)&DNAME& & & & & & & & & & & & & & & & & & & & & & & & & & & & & VARCHAR2(14)&LOC& & & & & & & & & & & & & & & & & & & & & & & & & & & & & & VARCHAR2(13)
scott@CNMMBO& desc tb_&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_& & --&删除刚刚穿件的表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'
1 row created.
scott@CNMMBO&
Commit complete.
--&下面的查询可以看到新增记录50的loc为缺省值'BeiJing'scott@CNMMBO& select *
& & 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 *
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_
& & 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'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_
Table dropped.
--3、唯一约束遗失scott@CNMMBO& alter table dept modify (dname unique);
Table altered.
scott@CNMMBO& create table tb_dept as select *
Table created.
scott@CNMMBO& insert into tb_dept select 60,'DEV','ShangHai'
1 row created.
scott@CNMMBO&
Commit complete.
scott@CNMMBO& select * from tb_
& & 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')
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 & 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 & 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 & 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---------------------------------------------------------------- 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包
更多Oracle相关信息见 专题页面
相关资讯 & & &
& (07/09/:09)
　　　同意评论声明
　　　发表
尊重网上道德，遵守中华人民共和国的各项有关法律法规
承担一切因您的行为而直接或间接导致的民事或刑事法律责任
本站管理人员有权保留或删除其管辖留言中的任意内容
本站有权在网站内转载或引用您的评论
参与本评论即表明您已经阅读并接受上述条款create tabel as select 在建表时如何指定数据类型字段col数据类型默认为char(4)现在如果insert into test select 'abcdef' 会提示长度大如果insert into test select 'ab'可以插入但select * from test where col='ab';有时有数据，有时没有，可能是不同对char类型设置的问题特别是alter table test modify(col varchar2(20));后select * from test where col='ab';就永远不行而必须使用select * from test where col='ab
';& varchar2和char对末尾有空格 怎么有这个差别不想每次都用alter modify和能不能在建表时同时指定数据类型就是说有一个元表，然后再生成根据这个元表结构上一样（字段结构是一样的）表的时候用这种建表方式。按楼主的要求，你先建表，然后再插入数据不就行了么？create table test as select 'abcd'字段col数据类型默认为char(4)现在如果insert into test select 'abcdef' 会提示长度大如果insert into test select 'ab'可以插入但select * from test w……char是定长的，定义时规定其长度N，插入数据时会用空格补位，就是说该字段上的数据长度都是N至于“不想每次都用alter modify和update”，如果你以“create table test as select ”方式建表，我觉得可能不能避免。引用:create table test as select 'abcd'字段col数据类型默认为char(4)现在如果insert into test select 'abcdef' 会提示长度大如果insert into test select 'ab'可以插入但……这东西好。。。。--------------------------------------------------------------多写一句废话,尽量使用rtrim,不建议使用trim,因为char是在后面补空的,rtrim能多保真一些数据.2010年9月 Oracle大版内专家分月排行榜第三2010年7月 Oracle大版内专家分月排行榜第三
2011年3月 Oracle大版内专家分月排行榜第二2011年2月 Oracle大版内专家分月排行榜第二
2010年10月 Oracle大版内专家分月排行榜第三
2011年3月 Oracle大版内专家分月排行榜第二2011年2月 Oracle大版内专家分月排行榜第二
2010年10月 Oracle大版内专家分月排行榜第三
2010年9月 Oracle大版内专家分月排行榜第三2010年7月 Oracle大版内专家分月排行榜第三
2011年3月 Oracle大版内专家分月排行榜第二2011年2月 Oracle大版内专家分月排行榜第二
2010年10月 Oracle大版内专家分月排行榜第三
2011年3月 Oracle大版内专家分月排行榜第二2011年2月 Oracle大版内专家分月排行榜第二
2010年10月 Oracle大版内专家分月排行榜第三
本帖子已过去太久远了，不再提供回复功能。