読者です 読者をやめる 読者になる 読者になる

とあるプログラマの備忘録

都内某所に住むプログラマが自分用に備忘録を残すという趣旨のブログです。はてなダイアリーから移動しました!

マテリアライズド・ビューの設定方法

更新履歴
2009/07/13

    • 記述ミスがあったので修正


DBLINK経由でマテリアライズドビューの環境構築する方法

マスタDB(本データを持っている大本のDB)
サブDB(マテリアライズドビューを作成するDB)


※注意事項
※DBLINK経由でマテビューを作成するときは「ON COMMIT」を使用できません。
※マテリアライズドグループを作成する際にはマスタDBのマスタ化が必要になります。



※構築内容

リフレッシュは5分に一回の高速リフレッシュ、
マテビューのための専用のテーブルスペースを作成して、
そこにテーブルをつくるようにする。
マテビューはグループ化して同タイミングでリフレッシュするようにする。



①tnsnames.oraにマスタDBへの接続情報記述(サブDB)



SQL>DBLINK1 =
  (DESCRIPTION =
  (ADDRESS = (PROTOCOL = TCP)
  (HOST = 192.168.XX.XX)(PORT = 1521)
  )
  (CONNECT_DATA =
  (SERVER = DEDICATED)
  (SERVICE_NAME = service_name)
  )



・DBLINK1ってのはただの名前です。かってにわかり安い名前をつける。
・HOSTはマスタDBのIPまたはPC名を入れる。
・ポートはそのまま(デフォルトは1521)
・SERVICE_NAMEはSQLでこれを打てば表示される。

show parameters service_names


②DBLINKの権限付与(サブDB)

SQL>GRANT CREATE DATABASE LINK TO [userName];


③DBLINK作成(サブDB)

SQL>CREATE PUBLIC DATABASE LINK [DBLINK名] CONNECT TO [マスタDBに接続できるユーザID]
   IDENTIFIED BY [マスタDBに接続できるユーザパスワード]
   USING 'DBLINK1';

必ずプライベート権限でDBLINKを作成すること。
※USINGはさっき作成したtnsnames.oraでつけた名前
Oracleのglobal_nameを設定している場合はそれを基準に。


④テーブルスペース作成(マスタDB)

SQL>CREATE TABLESPACE [テーブルスペース名]
   DATAFAILE '/oradate/test/mv_space.dbf' size 500M


※ここのパスはデータファイルのパス例で書いてあるけど変えなくてはだめ。

⑤作成したテーブルスペースにマテリアライズド・ビューログ作成(マスタDB)

SQL>CREATE MATERIALIZED VIEW LOG ON [テーブル名];

※テーブル名にはマテリアライズドビューしたいテーブルの名前を入れる
※高速リフレッシュ(差分リフレッシュ)するときはマテビューログが必要


⑥テーブルスペース作成(サブDB)

SQL>CREATE TABLESPACE [テーブルスペース名]
   DATAFAILE '/oradate/test/mv_space.dbf' size 500M


※ここのパスはデータファイルのパス例で書いてあるけど変えなくてはだめ。


⑦リフレッシュ・グループ作成(サブDB)

SQL>BEGIN
   DBMS_REFRESH.MAKE (
   name => '[グループ名]',
     list => '',
     next_date => sysdate,
     interval => 'sysdate + 1/24/60*5',
     implicit_destroy => FALSE,
     rollback_seg => '',
     push_deferred_rpc => TRUE,
     refresh_after_errors => FALSE);
END;

※interval が5分に一回リフレッシュしてくれって事。
※その他はパラメータは必要に応じて変更
※nameは任意

⑧マテリアライズド・ビュー作成

SQL>CREATE MATERIALIZED VIEW [マテビュー名]
TABLESPACE [テーブルスペース名]
REFRESH fast #fastは高速リフレッシュの事
AS
SELECT * FROM
[テーブル名]@[DBLINK名]


※マテビュー名は任意
※テーブル名はマテビューログを作成したテーブルの名前を入れる
※テーブルスペースはさっき作ったテーブルスペース名
※DBLINK名はさっき作ったDBLINK名

⑨マテリアライズド・ビューグループに追加

BEGIN
  DBMS_REFRESH.ADD (
   name => '[グループ名]',
   list => '[マテビュー名]',
   lax => TRUE);
END;

※laxはすでに違うスケジュールにマテビューが登録されているときでも、今回の設定を優先するということ。
※こうやれば複数のマテビューが同タイミングでリフレッシュできるようになる。


⑩手動リフレッシュ

BEGIN
DBMS_REFRESH.REFRESH('[グループ名]');
END;

多少修正いたしました。DBLINKはPUBLIC!と書いておきながら、
DBLINKの例文がPUBLICになっていませんでした。お詫び申しあげます。


書いてある記事を参考に実装する場合は自己責任でお願いします。