使用dbms_metadata生成建表语句（r2笔记97天)

原创

jeanron100 2021-07-23 14:20:02 ©著作权

文章标签 其他 文章分类 代码人生

©著作权归作者所有：来自51CTO博客作者jeanron100的原创作品，请联系作者获取转载授权，否则将追究法律责任

有时候在工作中，可以使用exp/imp得到表的创建语句。
如果想得到关于table,index,constraint的语句，可以考虑使用dbms_metadata来实现。
我们可以使用如下的脚本来得到建表语句，对应的索引语句，和ref_constraint语句。
建表语句就不多说了,关于索引的部分，过滤了主键和唯一性索引的部分，这些语句会和建表语句中的constraint有一定的冲突，而foreign key的语句在建表语句中也不建议使用，这样会对其他表产生依赖，可以考虑单独生成这部分的语句，最后执行。
所以整个脚本会分为3个部分，建表语句，创建索引的语句和ref_constraint的部分。
sqlplus -s n1/n1 <<EOF
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 off;
col sql_text format a300
spool ref_constraint_$1.log
@ref_constraint_$1.sql
spool off
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 off;
col sql_text format a300
spool index_$1.log
@index_$1.sql
spool off
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
) ;