いきさつ
DBMS_METADATA.GET_DDLでは、なかなか自分の欲しい形で整形出力されないので、自分で作成してみました。
準備するもの
まず、以下のスクリプトをコピーして、"mviewexport.sql"というファイルを作成します。
このとき、以下の部分をメンテしてください。
| メンテ箇所 |
| in ('MVIEW1','MVIEW2'..) |
出力対象マテリアライズド・ビュー |
------------------------------------------------------------------
-- Copyright(c) 2016 pakkin. All Rights Reserved.
-- [改訂履歴]
-- 2016.02.26 作成
-- 2016.07.15 START WITHのスペルミスを修正,クエリーリライト＆ビルドモードオプション追加,他...
--
-- [実行方法]
-- 1)このテキストを、"mviewexport.sql"として作成します。
-- 2)作成したフォルダ上で、コマンドプロンプトを起動し、以下のコマンドを実行します。
-- sqlplus -s user/password@servicename @mviewexport.sql
-- 3)同一フォルダ上に、mviewimport.sqlが作成されます。テーブルを生成したい場合、以下のコマンドを実行します。
-- sqlplus -s user/password@servicename @mviewimport.sql
-- 4)コンパイル結果がmviewimport.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
set long 40000
COLUMN CMDLINE FORMAT A300
COLUMN COL_SEP FORMAT A4
COLUMN COLUMN_NAME FORMAT A32
COLUMN DATA_TYPE FORMAT A14
COLUMN DATA_LEN FORMAT A12
COLUMN DATA_DEFAULT_T FORMAT A7
COLUMN NULLABLE FORMAT A8
COLUMN DESCEND FORMAT A4
COLUMN COMMENTS FORMAT A150
------------------------------------------------------------------
-- __mviewexport1.sql(MVIEW DDL)
------------------------------------------------------------------
spool __mviewexport1.sql
prompt SPOOL MVIEW/?&1..sql
prompt prompt --DROP MATERIALIZED VIEW "?&?&1"
prompt prompt --/
prompt select 'CREATE MATERIALIZED VIEW "' || viw.MVIEW_NAME || '"(' as cmdline
prompt from USER_MVIEWS viw
prompt where viw.MVIEW_NAME = '?&?&1'
prompt /
prompt select decode(CLM.COLUMN_ID,1,' ',' ,') as COL_SEP
prompt , '"' || CLM.COLUMN_NAME || '"' as COLUMN_NAME
prompt , nvl2(CMN.COMMENTS,'-- ' || CMN.COMMENTS,null) as COMMENTS
prompt from USER_TAB_COLUMNS CLM
prompt , USER_COL_COMMENTS CMN
prompt where CLM.TABLE_NAME = CMN.TABLE_NAME(+)
prompt and CLM.COLUMN_NAME = CMN.COLUMN_NAME(+)
prompt and CLM.TABLE_NAME = '?&?&1'
prompt order by CLM.COLUMN_ID
prompt /
prompt select ') BUILD ' || viw.BUILD_MODE || ' REFRESH ' || viw.REFRESH_METHOD || ' ON ' || viw.REFRESH_MODE as cmdline
prompt from USER_MVIEWS viw
prompt where viw.MVIEW_NAME = '?&?&1'
prompt /
prompt select 'WITH PRIMARY KEY' as cmdline
prompt from USER_INDEXES idx
prompt where idx.TABLE_NAME = '?&?&1'
prompt and idx.INDEX_NAME like 'SYS@_C@_SNAP$_%' escape '@'
prompt group by
prompt idx.TABLE_NAME
prompt /
prompt select 'WITH ROWID' as cmdline
prompt from USER_INDEXES idx
prompt where idx.TABLE_NAME = '?&?&1'
prompt and idx.INDEX_NAME like 'I@_SNAP$_%' escape '@'
prompt group by
prompt idx.TABLE_NAME
prompt /
prompt select 'START WITH ' ||
prompt case when job.LAST_DATE is not null then 'SYSDATE' else
prompt 'TO_DATE(''' || to_char(job.NEXT_DATE,'YYYY/MM/DD HH24:MI:SS') || ''',''YYYY/MM/DD HH24:MI:SS'')'
prompt end || ' NEXT ' || job.INTERVAL as cmdline
prompt from USER_MVIEWS viw
prompt , USER_JOBS job
prompt where viw.MVIEW_NAME = '?&?&1'
prompt and job.WHAT like 'dbms_refresh.refresh(''%."' || viw.MVIEW_NAME || '"'');'
prompt /
prompt select 'ENABLE QUERY REWRITE' as cmdline
prompt from USER_MVIEWS viw
prompt where viw.MVIEW_NAME = '?&?&1'
prompt and viw.REWRITE_ENABLED = 'Y'
prompt /
prompt prompt AS
prompt select QUERY as cmdline
prompt , '/' as endline
prompt from USER_MVIEWS viw
prompt where viw.MVIEW_NAME = '?&?&1'
prompt /
------------------------------------------------------------------
-- __mviewexport1.sql(APPEND MVIEW-COMMENT)
------------------------------------------------------------------
prompt select case when sqlline = 1 then
prompt 'COMMENT ON MATERIALIZED VIEW "?&?&1" IS ''' || tblc.COMMENTS || ''''
prompt else '/' end as cmdline
prompt from USER_MVIEW_COMMENTS tblc
prompt , (select 1 as sqlline from dual union all select 2 from dual )
prompt where tblc.MVIEW_NAME = '?&?&1'
prompt and tblc.COMMENTS IS NOT NULL
prompt order by sqlline
prompt /
------------------------------------------------------------------
-- __mviewexport1.sql(APPEND COLUMN-COMMENT)
------------------------------------------------------------------
prompt select case when sqlline = 1 then
prompt 'COMMENT ON COLUMN "?&?&1".' || rpad('"' || colc.COLUMN_NAME || '"',32) || ' IS ''' || colc.COMMENTS || ''''
prompt else '/' end as cmdline
prompt from USER_COL_COMMENTS colc
prompt , USER_TAB_COLUMNS col
prompt , (select 1 as sqlline from dual union all select 2 from dual )
prompt where colc.TABLE_NAME = col.TABLE_NAME
prompt and colc.COLUMN_NAME = col.COLUMN_NAME
prompt and colc.TABLE_NAME = '?&?&1'
prompt and colc.COMMENTS IS NOT NULL
prompt order by col.COLUMN_ID, sqlline
prompt /
prompt SPOOL OFF
spool off
------------------------------------------------------------------
-- __mviewexport2.sql(APPEND INDEX)
------------------------------------------------------------------
spool __mviewexport2.sql
prompt SET LONG 70
prompt COLUMN INDEX_EXPRESSION FORMAT A70
prompt SPOOL MVIEW/?&1..sql APPEND
prompt select case when cons.CONSTRAINT_NAME = idx.INDEX_NAME then 'ALTER TABLE "' || idx.TABLE_NAME || '" ADD CONSTRAINT "' || idx.INDEX_NAME || '" PRIMARY KEY('
prompt when idx.INDEX_TYPE = 'FUNCTION-BASED BITMAP' then 'CREATE BITMAP INDEX "' || idx.INDEX_NAME || '" ON "' || idx.TABLE_NAME || '"('
prompt when idx.UNIQUENESS = 'UNIQUE' then 'CREATE UNIQUE INDEX "' || idx.INDEX_NAME || '" ON "' || idx.TABLE_NAME || '"('
prompt when idx.UNIQUENESS = 'NONUNIQUE' then 'CREATE INDEX "' || idx.INDEX_NAME || '" ON "' || idx.TABLE_NAME || '"('
prompt end cmdline
prompt from USER_INDEXES idx
prompt , USER_CONSTRAINTS cons
prompt where cons.TABLE_NAME(+) = idx.TABLE_NAME
prompt and cons.INDEX_NAME(+) = idx.INDEX_NAME
prompt and cons.CONSTRAINT_TYPE(+) = 'P'
prompt and idx.TABLE_NAME = '?&?&1'
prompt and idx.INDEX_NAME = '?&2'
prompt /
prompt select decode(col.COLUMN_POSITION,1,' ',' ,') as COL_SEP
prompt -- , nvl(exp.COLUMN_EXPRESSION, to_clob('"' || col.COLUMN_NAME || '"')) as INDEX_EXPRESSION ORA-0600回避
prompt , nvl2(exp.COLUMN_EXPRESSION,'/* EXPRESSION */','"' || col.COLUMN_NAME || '"') as COLUMN_NAME
prompt , exp.COLUMN_EXPRESSION as INDEX_EXPRESSION
prompt , decode(col.DESCEND,'DESC','DESC') as DESCEND
prompt from USER_INDEXES idx
prompt , USER_IND_COLUMNS col
prompt , USER_IND_EXPRESSIONS exp
prompt where idx.TABLE_NAME = col.TABLE_NAME
prompt and idx.INDEX_NAME = col.INDEX_NAME
prompt and idx.TABLE_NAME = '?&?&1'
prompt and idx.INDEX_NAME = '?&?&2'
prompt and col.TABLE_NAME = exp.TABLE_NAME(+)
prompt and col.INDEX_NAME = exp.INDEX_NAME(+)
prompt and col.COLUMN_POSITION = exp.COLUMN_POSITION(+)
prompt order by col.COLUMN_POSITION
prompt /
prompt prompt )
prompt prompt /
prompt SPOOL OFF
spool off
------------------------------------------------------------------
-- __mviewexport3.sql(RUN)
------------------------------------------------------------------
spool __mviewexport3.sql
select '$ECHO Output-MVIEW : ' || viw.MVIEW_NAME || '
?@__mviewexport1.sql ' || viw.MVIEW_NAME as cmdline
from USER_MVIEWS viw
where viw.MVIEW_NAME in ('MVIEW1','MVIEW2'..)
order by 1
/
spool __mviewexport3.sql APPEND
select '$ECHO Output-INDEX : ' || rpad(idx.INDEX_NAME,30) || ' ON ' || idx.TABLE_NAME || '
?@__mviewexport2.sql ' || idx.TABLE_NAME || ' ' || idx.INDEX_NAME as cmdline
from USER_INDEXES idx
where idx.TABLE_NAME in ('MVIEW1','MVIEW2'..)
and idx.INDEX_NAME not like 'I@_SNAP$_%' escape '@'
and idx.INDEX_NAME not like 'SYS@_C@_SNAP$_%' escape '@'
order by 1
/
spool off
------------------------------------------------------------------
-- mviewimport.sql
------------------------------------------------------------------
spool mviewimport.sql
prompt SPOOL mviewimport.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 : ' || viw.MVIEW_NAME || '
?$ECHO Create-MVIEW : ' || viw.MVIEW_NAME || '
?@MVIEW/' || viw.MVIEW_NAME || '.sql' as cmdline
from USER_MVIEWS viw
where viw.MVIEW_NAME in ('MVIEW1','MVIEW2'..)
order by 1
/
prompt SPOOL OFF
prompt $ECHO 全マテリアライズド・ビューの生成が完了しました。
prompt EXIT
spool off
set escape off
$MKDIR MVIEW >> __mviewexport1.log 2>> __mviewexport2.log
@__mviewexport3.sql
$del __mviewexport*.*
$echo スクリプトを生成しました。
exit
実行(DDL取得)
スクリプトの存在するフォルダ上でコマンドプロンプトを開き、以下のコマンドを実行します。
sqlplus -s user/password@servicename @mviewexport.sql
| 実行時に生成されるサブフォルダ |
| MVIEW |
マテリアライズド・ビューのCREATE文を生成 |
| 実行時に生成されるスクリプト |
| mviewimport.sql |
生成されたCREATE文を反映するコマンドを生成 |
実行(反映)
スクリプトの存在するフォルダ上でコマンドプロンプトを開き、以下のコマンドを実行します。
sqlplus -s user/password@servicename @mviewimport.sql
| 実行時に生成されるログ |
| mviewimport.log |
生成コマンドを実行時のエラー情報を格納 |