重庆思庄Oracle、Redhat认证学习论坛

标题: Oracle 导出建表语句 [打印本页]

作者: mahan 时间: 6 天前
标题: Oracle 导出建表语句
1，查看Oracle 建表语句

spool table.sql

BEGIN
DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'STORAGE', FALSE);
DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'TABLESPACE', FALSE);
DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'SEGMENT_ATTRIBUTES', FALSE);
DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'REF_CONSTRAINTS', FALSE);
DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'CONSTRAINTS', FALSE);
DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'PARTITIONING', FALSE);
END;
/

SELECT DBMS_METADATA.GET_DDL('TABLE', TABLE_NAME, 'Customer') AS DDL
FROM ALL_TABLES
WHERE OWNER = 'Customer';

SELECT DBMS_METADATA.GET_DDL('INDEX', INDEX_NAME, 'C66') AS DDL
FROM ALL_INDEXES
WHERE OWNER = 'Customer';

spool off;

2，查看Oracle建索引语句

set lines 200 pages 40000 long 100000
SET LONGCHUNKSIZE 100000
SPOOL index_ddl.sql

SELECT DBMS_METADATA.GET_DDL('INDEX', INDEX_NAME, OWNER)
FROM ALL_INDEXES
WHERE OWNER = 'Customer';

SPOOL OFF;

欢迎光临重庆思庄Oracle、Redhat认证学习论坛 (http://bbs.cqsztech.com/)