マテリアライズド・ビュー生成コマンド取得

-- Index --

・Top

・Softwares

▼親父の独り言集

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

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

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

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

いきさつ

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


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