当心 CREATE TABLE AS - 数据库编程

NMMBO> 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;

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

当心 CREATE TABLE AS(二)