使用dbms_metadata生成建表语句

有时候在工作中，可以使用exp/imp得到表的创建语句。

如果想得到关于table,index,constraint的语句，可以考虑使用dbms_metadata来实现。

我们可以使用如下的脚本来得到建表语句，对应的索引语句，和ref_constraint语句。

建表语句就不多说了,关于索引的部分，过滤了主键和唯一性索引的部分，这些语句会和建表语句中的constraint有一定的冲突，而foreign key的语句在建表语句中也不建议使用，这样会对其他表产生依赖，可以考虑单独生成这部分的语句，最后执行。

所以整个脚本会分为3个部分，建表语句，创建索引的语句和ref_constraint的部分。

sqlplus -s n1/n1

SET SERVEROUTPUT ON;

SET LINESIZE 500;

SET FEEDBACK OFF;

set long 99999999     ;  

SET PAGESIZE 1000 ; 

set head off;

EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',false);

EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'PRETTY',true);

EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',true);

EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'TABLESPACE',true);

EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SEGMENT_ATTRIBUTES',false);

EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'REF_CONSTRAINTS',false);

spool image_copy_$1.sql

select 'select  DBMS_METADATA.GET_DDL('||chr(39)||'TABLE'||chr(39)||','||chr(39)||table_name||chr(39)||','||chr(39)||owner||chr(39)||') sql_text from dual; 'FROM all_tables where owner=sys_context('USERENV','current_user')  and table_name =upper('$1');

spool off;

col sql_text format a300

spool image_copy_$1.log

@image_copy_$1.sql

spool off

spool ref_constraint_$1.sql

select 'select  DBMS_METADATA.GET_DDL('||chr(39)||'REF_CONSTRAINT'||chr(39)||','||chr(39)||constraint_name||chr(39)||','||chr(39)||owner||chr(39)||') sql_text from dual; 'FROM all_constraints where owner=sys_context('USERENV','current_user')  and table_name =upper('$1') and constraint_type='R';

spool ref_constraint_$1.log

@ref_constraint_$1.sql

spool index_$1.sql

select 'select  DBMS_METADATA.GET_DDL('||chr(39)||'INDEX'||chr(39)||','||chr(39)||index_name||chr(39)||','||chr(39)||owner||chr(39)||') sql_text from dual;'FROM all_indexes where owner=sys_context('USERENV','current_user')  and table_name =upper('$1') and index_name in (

select index_name from user_indexes where table_name='$1' and index_name not in (select constraint_name from user_constraints where constraint_type in ('P','U' ) and UNIQUENESS='UNIQUE')

union

select index_name from user_indexes where table_name='$1' and index_name in (select constraint_name from user_constraints where constraint_type='C' ) and UNIQUENESS='NONUNIQUE'

);

spool index_$1.log

@index_$1.sql

EOF

运行脚本得到一个简单的例子。

  CREATE TABLE "N1"."PM9_CRDT_LMT_NOTIFICATION"

   (    "CYCLE_CODE" NUMBER(2,0) DEFAULT 0 CONSTRAINT "PM9CRDLT_CYCLE_CODE_NN" NOT NULL ENABLE,

        "CYCLE_MONTH" NUMBER(2,0) CONSTRAINT "PM9CRDLT_CYCLE_MONTH_NN" NOT NULL ENABLE,

        "SYS_CREATION_DATE" DATE CONSTRAINT "PM9CRDLT_SYS_CREATION_DATE_NN" NOT NULL ENABLE,

        "SYS_UPDATE_DATE" DATE,

        "OPERATOR_ID" NUMBER(9,0),

        "APPLICATION_ID" CHAR(6),

        "DL_SERVICE_CODE" CHAR(5),

        "DL_UPDATE_STAMP" NUMBER(4,0),

        "CYCLE_YEAR" NUMBER(4,0) CONSTRAINT "PM9CRDLT_CYCLE_YEAR_NN" NOT NULL ENABLE,

        "CUSTOMER_ID" NUMBER(9,0) CONSTRAINT "PM9CRDLT_CUSTOMER_ID_NN" NOT NULL ENABLE,

        "AGREEMENT_ID" NUMBER(9,0) CONSTRAINT "PM9CRDLT_AGREEMENT_ID_NN" NOT NULL ENABLE,

        "OFFER_INSTANCE" NUMBER(9,0) CONSTRAINT "PM9CRDLT_OFFER_INSTANCE_NN" NOT NULL ENABLE,

        "ITEM_ID" NUMBER(9,0) CONSTRAINT "PM9CRDLT_ITEM_ID_NN" NOT NULL ENABLE,

        "UNBILLED_UC_AMOUNT" NUMBER(11,4),

        "LAST_THRESHOLD" NUMBER(11,4),

        "LAST_ACTUAL_THRESHOLD" NUMBER(11,4),

        "CREDIT_LIMIT" NUMBER(11,4),

        "TOTAL_OBLIGATION" NUMBER(11,4),

        "HOLIDAY_IND" VARCHAR2(1),

         CONSTRAINT "PM9_CRDT_LMT_NOTIFICATION_PK" PRIMARY KEY ("CYCLE_CODE", "CYCLE_MONTH", "CYCLE_YEAR", "CUSTOMER_ID", "AGREEMENT_ID", "OFFER_INSTANCE", "ITEM_ID") ENABLE

   ) ;

可以看到得到的语句是期望之中的，如果有其他的索引信息，也都能得到相应的语句。