いきさつ
DBMS_METADATA.GET_DDLでは、なかなか自分の欲しい形で整形出力されないので、自分で作成してみました。
準備するもの
まず、以下のスクリプトをコピーして、"synonymexport.sql"というファイルを作成します。
------------------------------------------------------------------
-- Copyright(c) 2016 pakkin. All Right Reserved.
-- [改訂履歴]
-- 2016.07.25 作成
--
-- [実行方法]
-- 1)このテキストを、"synonymexport.sql"として作成します。
-- 2)作成したフォルダ上で、コマンドプロンプトを起動し、以下のコマンドを実行します。
-- sqlplus -s user/password@servicename @synonymexport.sql
-- 3)同一フォルダ上に、"synonymimport.sql"が作成されます。ソースを反映したい場合、以下のコマンドを実行します。
-- sqlplus -s user/password@servicename @synonymimport.sql
-- 4)コンパイル結果が"synonymimport.log"として出力されます。
------------------------------------------------------------------
set escape ?
set linesize 4000
set pagesize 0
set term off
set head off
set feed off
set trim on
set trimspool on
set verify off
------------------------------------------------------------------
-- __synonymexport1.sql(SYNONYM DDL)
------------------------------------------------------------------
spool __synonymexport1.sql
prompt SPOOL SYNONYM/?&1..sql
prompt prompt CREATE OR REPLACE SYNONYM "?&?&1" FOR
prompt select case when syn.TABLE_OWNER is not null then syn.TABLE_OWNER || '.' end
prompt || syn.TABLE_NAME
prompt || case when syn.DB_LINK is not null then '@' || syn.DB_LINK end as CMDLINE
prompt from USER_SYNONYMS syn
prompt where syn.SYNONYM_NAME = '?&?&1'
prompt /
prompt prompt /
prompt SPOOL OFF
spool off
------------------------------------------------------------------
-- __synonymexport2.sql(PUBLIC SYNONYM DDL)
------------------------------------------------------------------
spool __synonymexport2.sql
prompt SPOOL PUBLIC_SYNONYM/?&1..sql
prompt prompt CREATE OR REPLACE PUBLIC SYNONYM "?&?&1" FOR
prompt select case when syn.TABLE_OWNER is not null then syn.TABLE_OWNER || '.' end
prompt || syn.TABLE_NAME
prompt || case when syn.DB_LINK is not null then '@' || syn.DB_LINK end as CMDLINE
prompt from DBA_SYNONYMS syn
prompt where syn.SYNONYM_NAME = '?&?&1'
prompt and syn.OWNER = 'PUBLIC'
prompt /
prompt prompt /
prompt SPOOL OFF
spool off
------------------------------------------------------------------
-- __synonymexport3.sql(RUN)
------------------------------------------------------------------
spool __synonymexport3.sql
select '$ECHO Output-SYNONYM : ' || rpad(syn.SYNONYM_NAME,30) || case when syn.OWNER = 'PUBLIC' then '(PUBLIC)' end ||
case when syn.OWNER != 'PUBLIC' then '
?@__synonymexport1.sql ' else '
?@__synonymexport2.sql '
end || syn.SYNONYM_NAME as cmdline
from DBA_SYNONYMS syn
where exists(
select *
from DBA_OBJECTS obj
where obj.OWNER = syn.OWNER
and obj.OBJECT_NAME = syn.SYNONYM_NAME
and obj.OBJECT_TYPE = 'SYNONYM'
and obj.ORACLE_MAINTAINED = 'N'
--パブリック・シノニムを除外したい場合は、下記条件から"PUBLIC"を除外します。
) and syn.OWNER in ('PUBLIC', USER)
--対象を絞りたい場合はここに条件を追加します。
order by 1
/
spool off
------------------------------------------------------------------
-- synonymimport.sql
------------------------------------------------------------------
spool synonymimport.sql
prompt spool synonymimport.log
prompt set linesize 300
prompt set pagesize 0
prompt set term off
prompt set head off
prompt set feed on
prompt set trim on
prompt set trimspool on
prompt set verify off
select 'PROMPT Create-SYNONYM : ' || rpad(syn.SYNONYM_NAME,30) || case when syn.OWNER = 'PUBLIC' then '(PUBLIC)' end || '
?$ECHO Create-SYNONYM : ' || rpad(syn.SYNONYM_NAME,30) || case when syn.OWNER = 'PUBLIC' then '(PUBLIC)' end ||
case when syn.OWNER != 'PUBLIC' then '
?@SYNONYM/' else '
?@PUBLIC_SYNONYM/' end || syn.SYNONYM_NAME || '.sql
prompt' as cmdline
from DBA_SYNONYMS syn
where exists(
select *
from DBA_OBJECTS obj
where obj.OWNER = syn.OWNER
and obj.OBJECT_NAME = syn.SYNONYM_NAME
and obj.OBJECT_TYPE = 'SYNONYM'
and obj.ORACLE_MAINTAINED = 'N'
--パブリック・シノニムを除外したい場合は、下記条件から"PUBLIC"を除外します。
) and syn.OWNER in ('PUBLIC', USER)
--対象を絞りたい場合はここに条件を追加します。
order by 1
/
prompt SPOOL OFF
prompt $ECHO 全シノニムを生成しました。
prompt EXIT
spool off
set escape off
--パブリック・シノニムを除外したい場合は、"PUBLIC_SYNONYM"ディレクトリ作成をコメントアウトします。
$MKDIR SYNONYM >> __synonymexport1.log 2>> __synonymexport2.log
$MKDIR PUBLIC_SYNONYM >> __synonymexport1.log 2>> __synonymexport2.log
@__synonymexport3.sql
$del __synonymexport*.*
$echo スクリプトを生成しました。
exit
実行(DDL取得)
スクリプトの存在するフォルダ上でコマンドプロンプトを開き、以下のコマンドを実行します。
sqlplus -s user/password@servicename @synonymexport.sql
| 実行時に生成されるサブフォルダ |
| DBLINK |
シノニムのCREATE文を生成 |
| PUBLIC_DBLINK |
パブリック・シノニムのCREATE文を生成 |
| 実行時に生成されるスクリプト |
| synonymimport.sql |
生成されたCREATE文を反映するコマンドを生成 |
実行(反映)
スクリプトの存在するフォルダ上でコマンドプロンプトを開き、以下のコマンドを実行します。
sqlplus -s user/password@servicename @synonymimport.sql
| 実行時に生成されるログ |
| synonymimport.log |
生成コマンドを実行時のエラー情報を格納 |