-- How to use ddl.sql-- Run ddl.sql on the sql*plus.-- Login the sql*plus with apps user or dba user-- Start ddl.sql, which will ask you table_name and table_owner that you're looking for.-- It will generate tablename_ddl.txtset long 1000000SET PAGESIZE 3000set lines 200SET HEADING OFFSET VERIFY OFFSET FEEDBACK OFFset echo onset timing offset wrap OnACCEPT TABLE_NAME CHAR PROMPT 'Enter Table Name : 'ACCEPT TABLE_OWNER CHAR PROMPT 'Enter Table Owner : 'select DBMS_METADATA.GET_DDL('TABLE',OBJECT_NAME,OWNER)FROM Dba_objectswhere owner = UPPER('&TABLE_OWNER') and object_name = UPPER('&TABLE_NAME')and object_type = 'TABLE'union allselect dbms_metadata.GET_DEPENDENT_DDL ('COMMENT', TABLE_NAME, OWNER )FROM (select table_name,ownerfrom Dba_col_commentswhere owner = UPPER('&TABLE_OWNER')and table_name = UPPER('&TABLE_NAME')and comments is not nullunionselect table_name,ownerfrom sys.Dba_TAB_commentswhere owner = UPPER('&TABLE_OWNER')and table_name = UPPER('&TABLE_NAME')and comments is not null)union allselect DBMS_METADATA.GET_DEPENDENT_DDL('INDEX',TABLE_NAME, TABLE_OWNER)FROM (select table_name,table_ownerFROM Dba_indexeswhere table_owner = UPPER('&TABLE_OWNER')and table_name = UPPER('&TABLE_NAME')and index_name not in (select constraint_namefrom sys.Dba_constraintswhere table_name = table_nameand constraint_type = 'P' )and rownum = 1)union allselect dbms_metadata.GET_DDL ('TRIGGER', trigger_name ,owner )from Dba_triggerswhere table_owner = UPPER('&TABLE_OWNER')and table_name = UPPER('&TABLE_NAME').SET CONCAT +spool &TABLE_NAME+_ddl.txt/spool off

TABLESPACE "SYSTEM"

本文转自maclean_007 51CTO博客，原文链接: