create table as
来源:互联网 发布:js生态圈 编辑:程序博客网 时间:2024/04/28 12:03
对 DBA 而言,CREATE TABLE AS 可谓是家常便饭,顺手拈来。需不知该方式虽然简单,但疏忽也容易导致意想不到的问题。笔者前阵子就碰上
了这样的事情。由于是对原表进行克隆,且数据存储在不同的表空间,因此毫不犹豫地使用了CREATE TABLE AS,结果在运行package时,error...
了这样的事情。由于是对原表进行克隆,且数据存储在不同的表空间,因此毫不犹豫地使用了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_deptTable 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
- create table as......
- 当心 CREATE TABLE AS
- create table as
- CREATE TABLE AS
- create table .... as注意事项
- Create table as select
- create table as select
- create table table_name as ...
- oracle create table as select
- Oracle Create Table as Select
- 浅谈create table as select
- insert table 和create table as 区别
- postgres create table as select & create table like
- MySQL create table as与create table like对比
- MySql create table ... as select * from ..
- Select into from 与 Create Table As
- create table as select性能测试
- create table as 产生的问题
- POJ2429 Pollard rho因子分解
- 以WIFI模式调试Android手机
- MYSQL数据类型详细介绍
- 融资750万美元,Prism Skylabs释放监控录像的价值,做线下店铺的Google Analytics
- 注重原则和智慧结晶——读《浮现式设计》有感
- create table as
- WIN7_64位系统安装oracle以及PLSQL方法
- Cxf拦截器
- POJ2389《Bull Math》方法:高精度 模拟
- Data Guard——使用 RMAN 创建单实例物理(physical) standby 数据库
- winsock重叠IO模型
- 常用中文字体英文名称对照表
- 网站登录中的验证码作用
- 配置内核并使用iptables做端口映射