Script:Generate A DDL Script For A Table

以下脚本用于生成创建表的DDL语句,需要用到DBMS_METADATA.GET_DDL：

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

Sample output:

SQL> @ddl
SQL> set timing off
SQL> set wrap On
SQL>
SQL> ACCEPT TABLE_NAME CHAR PROMPT 'Enter Table Name : '
Enter Table Name : TAB$
SQL> ACCEPT TABLE_OWNER CHAR PROMPT 'Enter Table Owner : '
Enter Table Owner : SYS
SQL>
SQL> select DBMS_METADATA.GET_DDL('TABLE',OBJECT_NAME,OWNER)
  2  FROM Dba_objects
  3  where owner = UPPER('&TABLE_OWNER') and object_name = UPPER('&TABLE_NAME')
  4  and object_type = 'TABLE'
  5  union all
  6  select dbms_metadata.GET_DEPENDENT_DDL ('COMMENT', TABLE_NAME, OWNER )
  7  FROM (select table_name,owner
  8  from Dba_col_comments
  9  where owner = UPPER('&TABLE_OWNER')
 10  and table_name = UPPER('&TABLE_NAME')
 11  and comments is not null
 12  union
 13  select table_name,owner
 14  from sys.Dba_TAB_comments
 15  where owner = UPPER('&TABLE_OWNER')
 16  and table_name = UPPER('&TABLE_NAME')
 17  and comments is not null)
 18  union all
 19  select DBMS_METADATA.GET_DEPENDENT_DDL('INDEX',TABLE_NAME, TABLE_OWNER)
 20  FROM (select table_name,table_owner
 21  FROM Dba_indexes
 22  where table_owner = UPPER('&TABLE_OWNER')
 23  and table_name = UPPER('&TABLE_NAME')
 24  and index_name not in (select constraint_name
 25  from sys.Dba_constraints
 26  where table_name = table_name
 27  and constraint_type = 'P' )
 28  and rownum = 1)
 29  union all
 30  select dbms_metadata.GET_DDL ('TRIGGER', trigger_name ,owner )
 31  from Dba_triggers
 32  where table_owner = UPPER('&TABLE_OWNER')
 33  and table_name = UPPER('&TABLE_NAME')
 34  .
SQL> SET CONCAT +
SQL> spool &TABLE_NAME+_ddl.txt
SP2-0332: Cannot create spool file.
SQL> /


  CREATE TABLE "SYS"."TAB$"
   (    "OBJ#" NUMBER NOT NULL ENABLE,
        "DATAOBJ#" NUMBER,
        "TS#" NUMBER NOT NULL ENABLE,
        "FILE#" NUMBER NOT NULL ENABLE,
        "BLOCK#" NUMBER NOT NULL ENABLE,
        "BOBJ#" NUMBER,
        "TAB#" NUMBER,
        "COLS" NUMBER NOT NULL ENABLE,
        "CLUCOLS" NUMBER,
        "PCTFREE$" NUMBER NOT NULL ENABLE,
        "PCTUSED$" NUMBER NOT NULL ENABLE,
        "INITRANS" NUMBER NOT NULL ENABLE,
        "MAXTRANS" NUMBER NOT NULL ENABLE,
        "FLAGS" NUMBER NOT NULL ENABLE,
        "AUDIT$" VARCHAR2(38) NOT NULL ENABLE,
        "ROWCNT" NUMBER,
        "BLKCNT" NUMBER,
        "EMPCNT" NUMBER,
        "AVGSPC" NUMBER,
        "CHNCNT" NUMBER,
        "AVGRLN" NUMBER,
        "AVGSPC_FLB" NUMBER,
        "FLBCNT" NUMBER,
        "ANALYZETIME" DATE,
        "SAMPLESIZE" NUMBER,
        "DEGREE" NUMBER,
        "INSTANCES" NUMBER,
        "INTCOLS" NUMBER NOT NULL ENABLE,
        "KERNELCOLS" NUMBER NOT NULL ENABLE,
        "PROPERTY" NUMBER NOT NULL ENABLE,
        "TRIGFLAG" NUMBER,
        "SPARE1" NUMBER,
        "SPARE2" NUMBER,
        "SPARE3" NUMBER,
        "SPARE4" VARCHAR2(1000),
        "SPARE5" VARCHAR2(1000),
        "SPARE6" DATE
   ) CLUSTER "SYS"."C_OBJ#" ("OBJ#")



  CREATE INDEX "SYS"."I_TAB1" ON "SYS"."TAB$" ("BOBJ#")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "SYSTEM"