Oracle マテビュー作成DDLを取得

SQL*Plusを使いパラメータで指定するマテビューのDDLをファイル出力します。手順は簡単コピペだけの２ステップです。（同時にインデックス、コメントの出力も可能なので便利）

手順１　SQLファイル（get_mview_dd.sql）を作成する。

まずは、適当なフォルダにget_mview_dd.sqlという名前のSQLファイルを作成します。記載する内容は以下の通り。(有効行は全てASCIIコードのはずなのでShift_JIS、utf-8どちらで作成しても動作するはずですが基本はクライアント環境にあわせてSQLファイルを作成してください）

SET LIN 20000
-- １行に収まらない内場合改行して表示
SET WRAP ON
SET PAGESIZE 0
SET LONG 400000
SET LONGCHUNKSIZE 400000
-- 問い合わせの結果レコード件数表示制御
SET FEEDBACK OFF
-- コマンド表示制御
SET ECHO OFF
-- コマンド結果表示制御
SET TERMOUT OFF

-- 行末の空白除去
SET TRIMS ON
-- バインド変数新旧表示除去
SET VERIFY OFF

--DDLの終わりにセミコロン
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM( DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',TRUE );

-- PK情報はalter tableで表現
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'CONSTRAINTS_AS_ALTER', TRUE );

-- STORAGE属性を省略
--EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'STORAGE', FALSE );

-- --セグメント属性省略
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM (DBMS_METADATA.SESSION_TRANSFORM,'SEGMENT_ATTRIBUTES',FALSE);
-- 
-- 表領域を省略
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM( DBMS_METADATA.SESSION_TRANSFORM,'TABLESPACE',FALSE );
-- 
-- --パーティション省略
--EXEC DBMS_METADATA.SET_TRANSFORM_PARAM (DBMS_METADATA.SESSION_TRANSFORM,'PARTITIONING',FALSE);
-- 

-- スキーマ名省略
--EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(dbms_metadata.SESSION_TRANSFORM, 'EMIT_SCHEMA', FALSE);

--出力ファイル名変数定義
-- 『@』はスプールファイル名に使えないようなので、『_』に置き換える  2021/06/04
col spoolout_file_name new_value spool_file_name format a50
select replace('MVIEW_'||'&1'||'.'||'&2'||'.sql','@','_') spoolout_file_name 
from dual;

spool &spool_file_name

-- MATERIALIZED_VIEW用DDLの取得
select dbms_metadata.get_ddl('MATERIALIZED_VIEW',t.MVIEW_NAME,owner) from dba_mviews t
where owner= '&1' and t.MVIEW_NAME = '&2'
;

-- INDEXの取得（PKはcreate tableに含めているので外す）
select dbms_metadata.get_ddl('INDEX',t.INDEX_NAME,t.OWNER )
from dba_indexes t
left outer join DBA_CONSTRAINTS c
on t.OWNER =c.INDEX_OWNER and t.INDEX_NAME = c.INDEX_NAME
where t.OWNER= '&1' and t.TABLE_NAME ='&2'
and c.CONSTRAINT_TYPE is null
;

-- TABLEの取得（テーブル、カラム何れにもコメントが設定されていない場合
-- エラーになるのでis not nullの条件を追加している
select DBMS_METADATA.GET_DEPENDENT_DDL('COMMENT',t.TABLE_NAME,t.OWNER)
from (
  select c.OWNER,c.TABLE_NAME
  from DBA_COL_COMMENTS c
  where c.OWNER = '&1'
  and c.TABLE_NAME = '&2'
  and c.COMMENTS is not null
  group by c.OWNER,c.TABLE_NAME
  union
  select b.OWNER,b.MVIEW_NAME as TABLE_NAME  from DBA_MVIEW_COMMENTS b
  where b.OWNER = '&1'
  and b.MVIEW_NAME = '&2'
  and b.COMMENTS is not null
) t
;
spool off;

exit

手順２．実際にsqlplusを使いDDLを出力する。

SQL*plusをサイレントモード（-sオプション）で動作させ上記作成したSQLファイルを@で指定し、出力対象のテーブル名を指定します。
以下はDABADAスキーマが持つマテビュー（MATERIALIZED VIEW）「PREFECTURE_MMV_T」のDDLを出力する例です。パラメータとして渡すスキーマ名、トリガー名は大文字／小文字意識するのでご利用の際はご注意ください

sqlplus -s system/pasword@vm102/ora19.box @get_mview_dd.sql DABADA PREFECTURE_MMV_T

するとSQL*Plus実行フォルダに以下DDLファイルが作成されます。
MVIEW_DABADA.PREFECTURE_MMV_T.sql

以下はWindows環境（PowerShell）環境での実行結果です

Dos窓であれば平気なのですが、PowerShellでは『’@get_mview_dd.sql’』とシングルクォーテーションで外部SQLファイルを囲む必要あります。

PS D:\work> sqlplus -s system/password@vm013/orau8 '@get_mview_dd.sql' DABADA PREFECTURE_MMV_T
PS D:\work> ls -l MVIEW_DABADA.PREFECTURE_MMV_T.sql


    ディレクトリ: D:\work


Mode                 LastWriteTime         Length Name
----                 -------------         ------ ----
-a----        2022/11/21     23:02           1143 MVIEW_DABADA.PREFECTURE_MMV_T.sql


PS D:\work> Get-Content MVIEW_DABADA.PREFECTURE_MMV_T.sql

  CREATE MATERIALIZED VIEW "DABADA"."PREFECTURE_MMV_T" ("PREFID", "NAME", "NAME_R", "REGION_NAME")
  ORGANIZATION HEAP PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS NOLOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  BUILD IMMEDIATE
  USING INDEX
  REFRESH FORCE ON DEMAND START WITH sysdate+0 NEXT SYSDATE + 1/24/60
  USING DEFAULT LOCAL ROLLBACK SEGMENT
  USING ENFORCED CONSTRAINTS DISABLE QUERY REWRITE
  AS select p.prefid,p.name,p.name_r,r.name as region_name
from sooni.OY_PREFECTURE p inner join sooni.OY_REGION r
on p.regin_id = r.region_id
 ;


  CREATE INDEX "DABADA"."IDX01_PREFECTURE_MMV_T" ON "DABADA"."PREFECTURE_MMV_T" ("PREFID")
  ;


   COMMENT ON COLUMN "DABADA"."PREFECTURE_MMV_T"."PREFID" IS '都道府県ID';
   COMMENT ON COLUMN "DABADA"."PREFECTURE_MMV_T"."NAME" IS '都道府県名';
   COMMENT ON COLUMN "DABADA"."PREFECTURE_MMV_T"."NAME_R" IS '都道府県英字';
   COMMENT ON MATERIALIZED VIEW "DABADA"."PREFECTURE_MMV_T"  IS '都道府県MMV';

PS D:\work>