转自: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.结论
- create table as时,尽管会克隆表及数据,数据是完整的,但是结构部分仅仅是部分克隆
- create table as时,会使表上的约束被遗失或出于非正常状态
- create table as时,表上的索引、触发器等不会被同时克隆
- create table as 仅作测试使用,要得到完整的结构语句,还是使用dbms_metadata.get_ddl包
☆☆☆【不积跬步,无以至千里】☆☆☆
分类:
Oracle / 问题记录
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· DeepSeek “源神”启动!「GitHub 热点速览」
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· C# 集成 DeepSeek 模型实现 AI 私有化(本地部署与 API 调用教程)
· DeepSeek R1 简明指南:架构、训练、本地部署及硬件要求
· 2 本地部署DeepSeek模型构建本地知识库+联网搜索详细步骤