データベース・リンク生成コマンド取得

-- Index --

・Top

・Softwares

▼親父の独り言集

▼Oracleデータベースに対する独り言集

・ご利用の前に
・CSVデータ出力バッチ作成
・SQLLoader実行バッチ作成(制御ファイルもね)
・主キー作成・削除コマンド取得
・実行中セッションKILLコマンド取得
・PL/SQLソース反映コマンド取得
・テーブル生成コマンド取得
・ビュー生成コマンド取得
・マテリアライズド・ビュー生成コマンド取得
・マテリアライズド・ビューログ生成コマンド取得
・順序（シーケンス）生成コマンド取得
→データベース・リンク生成コマンド取得
・シノニム生成コマンド取得
・サブクエリーのアクセス順指定
・グループ単位の通し番号とグループ内の連番

・VB.NETやC#に対する独り言集

・我が家の家電事情について

いきさつ

DBMS_METADATA.GET_DDLでは、なかなか自分の欲しい形で整形出力されないので、自分で作成してみました。

準備するもの

まず、以下のスクリプトをコピーして、"dblinkexport.sql"というファイルを作成します。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
------------------------------------------------------------------
-- 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 生成コマンドを実行時のエラー情報を格納

Copyright(c) 2014-2022 pakkin. All Rights Reserved.