いきさつ
DBMS_METADATA.GET_DDLでは、なかなか自分の欲しい形で整形出力されないので、自分で作成してみました。
準備するもの
まず、以下のスクリプトをコピーして、"dblinkexport.sql"というファイルを作成します。
------------------------------------------------------------------
-- Copyright(c) 2016 pakkin. All Right Reserved.
-- [改訂履歴]
-- 2016.07.25 作成
--
-- [実行方法]
-- 1)このテキストを、"dblinkexport.sql"として作成します。
-- 2)作成したフォルダ上で、コマンドプロンプトを起動し、以下のコマンドを実行します。
-- sqlplus -s user/password@servicename @dblinkexport.sql
-- 3)同一フォルダ上に、"dblinkimport.sql"が作成されます。ソースを反映したい場合、以下のコマンドを実行します。
-- sqlplus -s user/password@servicename @dblinkimport.sql
-- 4)コンパイル結果が"dblinkimport.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
------------------------------------------------------------------
-- __dblinkexport1.sql(DBLINK DDL)
------------------------------------------------------------------
spool __dblinkexport1.sql
prompt SPOOL DBLINK/?&1..sql
prompt select 'ACCEPT DBLNKPWD CHAR FORMAT A30 PROMPT データベース・リンク[?&?&1]で接続するためのパスワードを入力してください: HIDE' as CMDLINE
prompt from USER_DB_LINKS lnk
prompt where lnk.DB_LINK = '?&?&1' and lnk.USERNAME is not null
prompt /
prompt prompt --DROP DATABASE LINK "?&?&1"
prompt prompt --/
prompt prompt CREATE DATABASE LINK "?&?&1"
prompt select 'CONNECT TO ' || lnk.USERNAME as CMDLINE from USER_DB_LINKS lnk where lnk.DB_LINK = '?&?&1' and lnk.USERNAME is not null union all
prompt select 'IDENTIFIED BY ?&' || 'DBLNKPWD' as CMDLINE from USER_DB_LINKS lnk where lnk.DB_LINK = '?&?&1' and lnk.USERNAME is not null union all
prompt select 'USING ''' || lnk.HOST || '''' as CMDLINE from USER_DB_LINKS lnk where lnk.DB_LINK = '?&?&1'
prompt /
prompt prompt /
prompt SPOOL OFF
spool off
------------------------------------------------------------------
-- __dblinkexport2.sql(PUBLIC DBLINK DDL)
------------------------------------------------------------------
spool __dblinkexport2.sql
prompt SPOOL PUBLIC_DBLINK/?&1..sql
prompt select 'ACCEPT DBLNKPWD CHAR FORMAT A30 PROMPT パブリック・データベース・リンク[?&?&1]で接続するためのパスワードを入力してください: HIDE' as CMDLINE
prompt from DBA_DB_LINKS lnk
prompt where lnk.OWNER = 'PUBLIC' and lnk.DB_LINK = '?&?&1' and lnk.USERNAME is not null
prompt /
prompt prompt --DROP PUBLIC DATABASE LINK "?&?&1"
prompt prompt --/
prompt prompt CREATE PUBLIC DATABASE LINK "?&?&1"
prompt select 'CONNECT TO ' || lnk.USERNAME as CMDLINE from ALL_DB_LINKS lnk where lnk.OWNER = 'PUBLIC' and lnk.DB_LINK = '?&?&1' and lnk.USERNAME is not null union all
prompt select 'IDENTIFIED BY ?&' || 'DBLNKPWD' as CMDLINE from ALL_DB_LINKS lnk where lnk.OWNER = 'PUBLIC' and lnk.DB_LINK = '?&?&1' and lnk.USERNAME is not null union all
prompt select 'USING ''' || lnk.HOST || '''' as CMDLINE from ALL_DB_LINKS lnk where lnk.OWNER = 'PUBLIC' and lnk.DB_LINK = '?&?&1'
prompt /
prompt prompt /
prompt SPOOL OFF
spool off
------------------------------------------------------------------
-- __dblinkexport3.sql(RUN)
------------------------------------------------------------------
spool __dblinkexport3.sql
select '$ECHO Output-DATABASE LINK : ' || rpad(lnk.DB_LINK,30) || case when lnk.OWNER = 'PUBLIC' then '(PUBLIC)' end ||
case when lnk.OWNER != 'PUBLIC' then '
?@__dblinkexport1.sql ' else '
?@__dblinkexport2.sql '
end || lnk.DB_LINK as cmdline
from DBA_DB_LINKS lnk
where exists(
select *
from DBA_OBJECTS obj
where obj.OWNER = lnk.OWNER
and obj.OBJECT_NAME = lnk.DB_LINK
and obj.OBJECT_TYPE = 'DATABASE LINK'
and obj.ORACLE_MAINTAINED = 'N'
) and lnk.DB_LINK != 'DBMS_CLRDBLINK'
--パブリック・データベース・リンクを除外したい場合は、下記条件から"PUBLIC"を除外します。
and lnk.OWNER in ('PUBLIC', USER)
--対象を絞りたい場合はここに条件を追加します。
order by 1
/
spool off
------------------------------------------------------------------
-- dblinkimport.sql
------------------------------------------------------------------
spool dblinkimport.sql
prompt spool dblinkimport.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-DATABASE LINK : ' || rpad(lnk.DB_LINK,30) || case when lnk.OWNER = 'PUBLIC' then '(PUBLIC)' end || '
?$ECHO Create-DATABASE LINK : ' || rpad(lnk.DB_LINK,30) || case when lnk.OWNER = 'PUBLIC' then '(PUBLIC)' end ||
case when lnk.OWNER != 'PUBLIC' then '
?@DBLINK/' else '
?@PUBLIC_DBLINK/' end || lnk.DB_LINK || '.sql
prompt' as cmdline
from DBA_DB_LINKS lnk
where exists(
select *
from DBA_OBJECTS obj
where obj.OWNER = lnk.OWNER
and obj.OBJECT_NAME = lnk.DB_LINK
and obj.OBJECT_TYPE = 'DATABASE LINK'
and obj.ORACLE_MAINTAINED = 'N'
) and lnk.DB_LINK != 'DBMS_CLRDBLINK'
--パブリック・データベース・リンクを除外したい場合は、下記条件から"PUBLIC"を除外します。
and lnk.OWNER in ('PUBLIC', USER)
--対象を絞りたい場合はここに条件を追加します。
order by 1
/
prompt SPOOL OFF
prompt $ECHO 全データベース・リンクを生成しました。
prompt EXIT
spool off
set escape off
--パブリック・データベース・リンクを除外したい場合は、"PUBLIC_DBLINK"ディレクトリ作成をコメントアウトします。
$MKDIR DBLINK >> __dblinkexport1.log 2>> __dblinkexport2.log
$MKDIR PUBLIC_DBLINK >> __dblinkexport1.log 2>> __dblinkexport2.log
@__dblinkexport3.sql
$del __dblinkexport*.*
$echo スクリプトを生成しました。
exit
実行(DDL取得)
スクリプトの存在するフォルダ上でコマンドプロンプトを開き、以下のコマンドを実行します。
sqlplus -s user/password@servicename @dblinkexport.sql
| 実行時に生成されるサブフォルダ |
| DBLINK |
データベース・リンクのCREATE文を生成 |
| PUBLIC_DBLINK |
パブリック・データベース・リンクのCREATE文を生成 |
| 実行時に生成されるスクリプト |
| dblinkimport.sql |
生成されたCREATE文を反映するコマンドを生成 |
実行(反映)
スクリプトの存在するフォルダ上でコマンドプロンプトを開き、以下のコマンドを実行します。
sqlplus -s user/password@servicename @dblinkimport.sql
| 実行時に生成されるログ |
| dblinkimport.log |
生成コマンドを実行時のエラー情報を格納 |