利用DBMS_METADATA取得TABLE INDEX TRIGGER创建的脚本

declare
    tabname varchar2(10) :='TEST';
    tabowner varchar2(10) :='TEST';
    t_str varchar2(2000);
    C_str varchar2(2000);
    I_str varchar2(2000);
    TR_str varchar2(2000);
    t_ddl varchar2(2000);
    C_ddl varchar2(2000);
    I_ddl varchar2(2000);
    TR_ddl varchar2(2000);
    cursor ddl_ta is
       select t_ddl,c_ddl,i_ddl,tr_ddl from (
       SELECT DBMS_METADATA.get_ddl ('TABLE',tabname,tabowner) t_ddl
       FROM all_tables
       WHERE wner =tabowner AND table_name =tabname
       UNION ALL
       SELECT DBMS_METADATA.get_dependent_ddl ('COMMENT',tabname,tabowner) c_ddl
       FROM (SELECT table_name, owner
       FROM all_col_comments
       WHERE wner =tabowner AND table_name =tabname AND comments IS NOT NULL
       UNION all
       SELECT table_name, owner
       FROM SYS.all_tab_comments
       WHERE wner = tabowner AND table_name =tabname AND comments IS NOT NULL)
       UNION ALL
       SELECT DBMS_METADATA.get_dependent_ddl ('INDEX',tabname,tabowner) i_ddl
       FROM all_indexes
       WHERE table_owner =tabowner
       AND table_name = tabname
       AND index_name NOT IN (
       SELECT constraint_name
       FROM SYS.all_constraints
       WHERE table_name =tabname
       AND constraint_type = 'P')
       AND uniqueness != 'UNIQUE'
       UNION ALL
       SELECT DBMS_METADATA.get_ddl ('TRIGGER',tabname,tabowner) tr_ddl
       FROM all_triggers
       WHERE table_owner =tabowner AND table_name =tabname);
begin
open ddl_ta;
  loop
    fetch ddl_ta into t_str,c_str,i_str,tr_str;
    exit when ddl_ta%notfound;
dbms_output.put_line('================get table_ddl script======================');
dbms_output.put_line(t_str);
       if c_str is not null then
dbms_output.put_line('================get comment_ddl script====================');
dbms_output.put_line(c_str);
          else
          if i_str is not null then
dbms_output.put_line('================get index_ddl script======================');
dbms_output.put_line(i_str);
            if tr_str is not null then
dbms_output.put_line('================get constr_ddl script=====================');
dbms_output.put_line(tr_str);
            end if;
         end if;
      end if;
end loop;
close ddl_ta;
end;
/

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7882490/viewspace-668083/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/7882490/viewspace-668083/