| 获取DDL脚本增强版 | DBA之路：OoNiceDream |

获取DDL脚本增强版

作者：OoNiceDream【转载时请务必以超链接形式标明文章原始出处和作者信息】
链接：http://www.dbaroad.me/archives/2009/10/get_tab_ddl.html

我们常常会在不同的数据库中创建相同的表，包括表结构、索引、约束、权限都要相同。这时我一般会用TOAD，因为抓取的语句比较全。但TOAD也有个问题，就是遇到表上对象、分区比较多时，常常会半天没响应，甚至卡死在哪里。还是写个脚本来实现吧：

set verify off feedback off
set long 1000000000
set longc  1000000000
set head off
set wrap on
set linesize 10000
 
define i_table_name = '&table_name'
define i_owner      = '&owner'
spool e:\tab_ddl.log
 
exec dbms_metadata.set_transform_param( DBMS_METADATA.SESSION_TRANSFORM, 'SQLTERMINATOR',TRUE);
exec dbms_metadata.set_transform_param( DBMS_METADATA.SESSION_TRANSFORM, 'STORAGE', false ); 
exec dbms_metadata.set_transform_param( DBMS_METADATA.SESSION_TRANSFORM, 'CONSTRAINTS_AS_ALTER', TRUE ); 
 
PROMPT -------------------  The TABLE DDL Sql -------------------
 
PROMPT
 
select dbms_metadata.get_ddl('TABLE',
                             upper('&i_table_name'),
                             upper('&i_owner'))
  from dual;
 
PROMPT -------------------  The Index DDL Sql -------------------
 
PROMPT
create global temporary table perfstat.tab_ddl_tmp (txt clob) on commit preserve rows;
 
declare
  a1 clob;
  cursor cur is
    select index_name
      from dba_indexes
     where table_name = upper('&i_table_name')
       and owner = upper('&i_owner');
begin
  for rec in cur loop
    insert into perfstat.tab_ddl_tmp
      select dbms_metadata.get_ddl('INDEX',
                                   rec.index_name,
                                   upper('&i_owner'))
        from dual;
    commit;
  end loop;
  commit;
end;
/
select * from perfstat.tab_ddl_tmp;
 
PROMPT -------------------  The Grant sql -------------------
 
PROMPT
select 'grant ' || PRIVILEGE || ' on ' || owner || '.' || table_name ||
       ' to ' || grantee || ';'
  from dba_tab_privs
 where table_name = upper('&i_table_name')
   and owner = upper('&i_owner');
 
spool off
 
truncate table perfstat.tab_ddl_tmp;
drop table perfstat.tab_ddl_tmp;
 
set head on
set feedback on
set wrap off
set linesize 150

运行结果：

sys@ORCL>@tab_ddl
输入 table_name 的值:  test
输入 owner 的值:  rockey
-------------------  The TABLE DDL Sql ------------------
 
 
 
  CREATE TABLE "ROCKEY"."TEST"
   (    "A" NUMBER NOT NULL ENABLE,
        "B" NUMBER DEFAULT 1 NOT NULL ENABLE,
        "C" VARCHAR2(10),
        "E" VARCHAR2(10 CHAR)
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  TABLESPACE "USERS" ;
  ALTER TABLE "ROCKEY"."TEST" ADD CONSTRAINT "CHECK_B" CHECK (B < 50) DISABLE;
  ALTER TABLE "ROCKEY"."TEST" ADD CONSTRAINT "TEST_PK" PRIMARY KEY ("A")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
  TABLESPACE "USERS"  ENABLE;
 
 
-------------------  The Index DDL Sql ------------------
 
 
 
  CREATE UNIQUE INDEX "ROCKEY"."IND_A" ON "ROCKEY"."TEST" ("A")
  PCTFREE 10 INITRANS 2 MAXTRANS 255
  TABLESPACE "USERS" ;
 
 
 
  CREATE INDEX "ROCKEY"."IND_B" ON "ROCKEY"."TEST" ("B")
  PCTFREE 10 INITRANS 2 MAXTRANS 255
  TABLESPACE "USERS" ;
 
 
-------------------  The Grant sql ------------------
 
 
grant INSERT on ROCKEY.TEST to PERFSTAT;
grant SELECT on ROCKEY.TEST to PERFSTAT;

关于脚本的说明：
9I中由于dbms_output.put_line的限制，DDL语句一般都无法完整地输出，所以改用先插入临时表，再select出来的方法，这个在写脚本时还是挺常用的。

约束的DDL语句是在获取表的DDL时就直接获取了，并另起一个ALTER语句来显示，通过以下语句来实现：

( DBMS_METADATA.SESSION_TRANSFORM, 'CONSTRAINTS_AS_ALTER', TRUE );

这些DDL语句的执行，索引的创建还是应该在约束之前的，例如有唯一性约束的表，先创建了约束，索引就自动创建了。所以可以参考以下顺序：表->索引->约束->授权

原本写脚本时就打算按这个顺序显示。约束可以从dba_constraints获取约束名，再循环查询出来，但9I中一些oracle自动添加的约束，如表的NOT NULL约束，在获取时就会报以下错误：

ORA-31603: object "SYS_C002986" of type CONSTRAINT not found in schema "ROCKEY"

在每个语句结束后加上分号（;）：

( DBMS_METADATA.SESSION_TRANSFORM, 'SQLTERMINATOR',TRUE);

不显示STORAGE子句：

( DBMS_METADATA.SESSION_TRANSFORM, 'STORAGE', false );

sqlplus参数说明：
最重要的是set wrap on与set longc 1000000000
如果设置为wrap off，clob在sqlplus中会无法显示。
如果不设置set longc，一些较长的又需要在同一行显示的语句就会换行，例如：
PARTITION “A_B_C_D_E_2007_OCT” VALUES LESS THAN (TO_DATE(’ 2007-11-01 00:00:00′, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’))这样的语句，最好能在同一行显示，防止执行出错。

不知道我这些小改进能不能算是增强版了，哈哈