Oracle 导出建表语句

mahan

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;