シノニム生成コマンド取得

-- Index --

・Top

・Softwares

▼親父の独り言集

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

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

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

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

いきさつ

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 生成コマンドを実行時のエラー情報を格納


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