Oracle get ddl with specifying schemas. スキーマ指定DDL取得SQL

オラクルで、指定スキーマのDDLを取得するSQLスクリプト。

スクリプト中の USER1, USER2, USER3 の箇所を、

取得したいスキーマ名に変更してください。

-- パラメータ設定
set lines  1000
set pages 0
set long   10000
set heading off
set trimspool on
set feedback off
set longchunk   10000

BEGIN
  -- 終端に;を出力
  -- output terminal character like ';'
  DBMS_METADATA.SET_TRANSFORM_PARAM(
    DBMS_METADATA.SESSION_TRANSFORM,
    'SQLTERMINATOR',
    TRUE);
END;
/

-- DDL of tables
spool ddl_table.sql
select
  dbms_metadata.get_ddl('TABLE',table_name,owner)
from
  all_tables
where
  owner in ( 'USER1','USER2','USER3' )
order by
  owner, table_name
;
spool off;

-- DDL of views
spool ddl_views.sql
select
  dbms_metadata.get_ddl('VIEW',view_name,owner)
from
  all_views
where
  owner in ( 'USER1','USER2','USER3' )
order by
  owner, view_name
;
spool off;

-- DDL of indexes
spool ddl_indexes.sql
select
  dbms_metadata.get_ddl('INDEX',index_name,owner)
from
  all_tables
where
  owner in ( 'USER1','USER2','USER3' )
order by
  owner, index_name
;
spool off;

-- DDL of constraints
spool ddl_constraints.sql
  select
    dbms_metadata.get_ddl('CONSTRAINT',constraint_name,owner)
  from
    all_constraints
  where
    constraint_type = 'P'
    and owner in ( 'USER1','USER2','USER3' )
  order by
    owner, constraint_name
union all
  select
    dbms_metadata.get_ddl('CONSTRAINT',constraint_name,owner)
  from
    all_constraints
  where
    constraint_type = 'C'
    and substr(constraint_name,1,4)!='SYS_'
    and owner in ( 'USER1','USER2','USER3' )
  order by
    owner, constraint_name
union all
  select
    -- take attention, foreign key is 'REF_CONSTRAINT'
    dbms_metadata.get_ddl('REF_CONSTRAINT',constraint_name,owner)
  from
    all_constraints
  where
    constraint_type = 'R'
    and owner in ( 'USER1','USER2','USER3' )
  order by
    owner, constraint_name
union all
  select
    dbms_metadata.get_ddl('CONSTRAINT',constraint_name,owner)
  from
    all_constraints
  where
    constraint_type not in ( 'P','R','C')
    and owner in ( 'USER1','USER2','USER3' )
  order by
    owner, constraint_name
;
spool off;

-- DDL of package
spool ddl_package.sql
select
  dbms_metadata.get_ddl('PACKAGE',name,owner)
from
  (select
     distinct owner, name
   from
     all_source
   where
     type = 'PACKAGE'
     and owner in ( 'USER1','USER2','USER3' )
   )
order by
  owner, name
;
spool off;

-- DDL of package body
spool ddl_package_body.sql
select
  dbms_metadata.get_ddl('PACKAGE_BODY',name,owner)
from
  (select
     distinct owner, name
   from
     all_source
   where
     type = 'PACKAGE'
     and owner in ( 'USER1','USER2','USER3' )
   )
order by
  owner, name
;
spool off;

-- 全オブジェクト型は以下のページを参照。

-- and so on, all of object types are below..
-- http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_metada.htm#BGBFJFHA