いきさつ
DBMS_METADATA.GET_DDLでは、なかなか自分の欲しい形で整形出力されないので、自分で作成してみました。
準備するもの
まず、以下のスクリプトをコピーして、"mviewlogexport.sql"というファイルを作成します。
このとき、以下の部分をメンテしてください。
| メンテ箇所 |
| in ('TABLE1','TABLE2'..) |
ログ対象テーブル |
------------------------------------------------------------------
-- Copyright(c) 2016 pakkin. All Rights Reserved.
-- [改訂履歴]
-- 2016.07.15 作成
--
-- [実行方法]
-- 1)このテキストを、"mviewlogexport.sql"として作成します。
-- 2)作成したフォルダ上で、コマンドプロンプトを起動し、以下のコマンドを実行します。
-- sqlplus -s user/password@servicename @mviewlogexport.sql
-- 3)同一フォルダ上に、mviewlogimport.sqlが作成されます。テーブルを生成したい場合、以下のコマンドを実行します。
-- sqlplus -s user/password@servicename @mviewlogimport.sql
-- 4)コンパイル結果がmviewlogimport.logとして出力されます。
------------------------------------------------------------------
set escape ?
set linesize 300
set pagesize 0
set term off
set head off
set feed off
set trim on
set trimspool on
set verify off
------------------------------------------------------------------
-- __mviewlogexport1.sql(MVIEW-LOG DDL)
------------------------------------------------------------------
spool __mviewlogexport1.sql
prompt SPOOL MVIEW_LOG/?&1..sql
prompt prompt --DROP MATERIALIZED VIEW LOG ON "?&?&1"
prompt prompt --/
prompt prompt CREATE MATERIALIZED VIEW LOG ON "?&?&1"
prompt select 'WITH '||
prompt case when mvl.ROWIDS = 'YES' and (mvl.PRIMARY_KEY = 'YES' or mvl.SEQUENCE = 'YES') then 'ROWID,'
prompt when mvl.ROWIDS = 'YES' and (mvl.PRIMARY_KEY != 'YES' and mvl.SEQUENCE != 'YES') then 'ROWID' end ||
prompt case when mvl.PRIMARY_KEY = 'YES' and mvl.SEQUENCE = 'YES' then 'PRIMARY KEY,'
prompt when mvl.PRIMARY_KEY = 'YES' and mvl.SEQUENCE != 'YES' then 'PRIMARY KEY' end ||
prompt case when mvl.SEQUENCE = 'YES' then 'SEQUENCE' end || (
prompt select '(' || listagg(MLC.COLUMN_NAME, ',') within group (order by MLC.COLUMN_NAME asc) || ')'
prompt from DBA_MVIEW_LOG_FILTER_COLS MLC
prompt where MLC.OWNER = USER
prompt and MLC.NAME = mvl.MASTER
prompt and not exists(
prompt select *
prompt from USER_CONS_COLUMNS CLM
prompt , USER_CONSTRAINTS CON
prompt where CLM.TABLE_NAME = MLC.NAME
prompt and CLM.COLUMN_NAME = MLC.COLUMN_NAME
prompt and CON.CONSTRAINT_TYPE = 'P'
prompt and CON.CONSTRAINT_NAME = CLM.CONSTRAINT_NAME
prompt and CON.TABLE_NAME = CLM.TABLE_NAME)
prompt group by
prompt mvl.MASTER
prompt having listagg(MLC.COLUMN_NAME, ',') within group (order by MLC.COLUMN_NAME asc) is not null
prompt ) as cmdline
prompt from USER_MVIEW_LOGS mvl
prompt where mvl.MASTER = '?&?&1'
prompt and (mvl.ROWIDS = 'YES' or mvl.PRIMARY_KEY = 'YES' or mvl.SEQUENCE = 'YES')
prompt /
prompt select 'INCLUDING NEW VALUES' as cmdline
prompt from USER_MVIEW_LOGS mvl
prompt where mvl.MASTER = '?&?&1'
prompt and mvl.INCLUDE_NEW_VALUES = 'YES'
prompt /
prompt prompt /
prompt SPOOL OFF
spool off
------------------------------------------------------------------
-- __mviewlogexport3.sql(RUN)
------------------------------------------------------------------
spool __mviewlogexport3.sql
select '$ECHO Output-MVIEW_LOG : ' || mvl.MASTER || '
?@__mviewlogexport1.sql ' || mvl.MASTER as cmdline
from USER_MVIEW_LOGS mvl
where mvl.MASTER in ('TABLE1', 'TABLE2'..)
order by 1
/
spool off
------------------------------------------------------------------
-- mviewlogimport.sql
------------------------------------------------------------------
spool mviewlogimport.sql
prompt SPOOL mviewlogimport.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-MVIEW_LOG : ' || mvl.MASTER || '
?$ECHO Create-MVIEW_LOG : ' || mvl.MASTER || '
?@MVIEW_LOG/' || mvl.MASTER || '.sql' as cmdline
from USER_MVIEW_LOGS mvl
where mvl.MASTER in ('TABLE1', 'TABLE2'..)
order by 1
/
prompt SPOOL OFF
prompt $ECHO 全マテリアライズド・ビュー・ログの生成が完了しました。
prompt EXIT
spool off
set escape off
$MKDIR MVIEW_LOG >> __mviewlogexport1.log 2>> __mviewlogexport2.log
@__mviewlogexport3.sql
$del __mviewlogexport*.*
$echo スクリプトを生成しました。
exit
実行(DDL取得)
スクリプトの存在するフォルダ上でコマンドプロンプトを開き、以下のコマンドを実行します。
sqlplus -s user/password@servicename @mviewlogexport.sql
| 実行時に生成されるサブフォルダ |
| MVIEW_LOG |
マテリアライズド・ビュー・ログのCREATE文を生成 |
| 実行時に生成されるスクリプト |
| mviewlogimport.sql |
生成されたCREATE文を反映するコマンドを生成 |
実行(反映)
スクリプトの存在するフォルダ上でコマンドプロンプトを開き、以下のコマンドを実行します。
sqlplus -s user/password@servicename @mviewlogimport.sql
| 実行時に生成されるログ |
| mviewlogimport.log |
生成コマンドを実行時のエラー情報を格納 |