Script:Generate A DDL Script For A Table

-- 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.txt

set long 1000000
SET PAGESIZE 3000
set lines 200
SET HEADING OFF
SET VERIFY OFF
SET FEEDBACK OFF
set echo on
set timing off
set wrap On

ACCEPT 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_objects
where owner = UPPER('&TABLE_OWNER') and object_name = UPPER('&TABLE_NAME')
and object_type = 'TABLE'
union all
select dbms_metadata.GET_DEPENDENT_DDL ('COMMENT', TABLE_NAME, OWNER )
FROM (select table_name,owner
from Dba_col_comments
where owner = UPPER('&TABLE_OWNER')
and table_name = UPPER('&TABLE_NAME')
and comments is not null
union
select table_name,owner
from sys.Dba_TAB_comments
where owner = UPPER('&TABLE_OWNER')
and table_name = UPPER('&TABLE_NAME')
and comments is not null)
union all
select DBMS_METADATA.GET_DEPENDENT_DDL('INDEX',TABLE_NAME, TABLE_OWNER)
FROM (select table_name,table_owner
FROM Dba_indexes
where table_owner = UPPER('&TABLE_OWNER')
and table_name = UPPER('&TABLE_NAME')
and index_name not in (select constraint_name
from sys.Dba_constraints
where table_name = table_name
and constraint_type = 'P' )
and rownum = 1)
union all
select dbms_metadata.GET_DDL ('TRIGGER', trigger_name ,owner )
from Dba_triggers
where 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博客，原文链接:
http://blog.51cto.com/maclean/1277125