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

kenken0807_DBメモ

つば九郎が好きなDBAです。Oracle Standard Editionでの運用やツールとかとかの備忘録。特に記載がない場合はoracle11gR2です。時々MySQL

Oracle 別スキーマ間でFAST REFRESH ON COMMITモードのマテリアライズドビューを作成する

スキーマMVIEW_MOTOのテーブルTESTに対して
スキーマMVIEW_SAKIFAST REFRESH ON COMMITモードのマテリアライズドビューを作成する

MVIEW_MOTOにテーブルとマテリアライズドビューログの作成

CREATE TABLE test (id NUMBER,id2 NUMBER,del_flg NUMBER,CONSTRAINT test_pk PRIMARY KEY(id));
CREATE MATERIALIZED VIEW LOG ON test;

権限の付与

  • MVIEW_SAKIMVIEW_MOTOtestテーブルとマテビューログに対してのSELECT権限を付与
GRANT SELECT ON mview_moto.test TO mview_saki;
GRANT SELECT ON mview_moto.MLOG$_TEST to mview_saki;

ちなみにマテビューログの名前はMLOG$_[tablename]となっている。
Oracleの場合オブジェクトの名称は30バイトまでなので30バイトのテーブル名称は以下のようになるみたい。
20バイトで切られる、または20バイト切った際に同様のMLOG名称がある場合は末尾に数値がつく

create table abcdefghijklmnopqrstuvwxyzabcD(id int primary key);
create materialized view log on abcdefghijklmnopqrstuvwxyzabcD;
→  MLOG$_ABCDEFGHIJKLMNOPQRST

create table abcdefghijklmnopqrstuvwxyzabcE(id int primary key);
create materialized view log on abcdefghijklmnopqrstuvwxyzabcE;
→  MLOG$_ABCDEFGHIJKLMNOPQRST1
  • MVIEW_SAKIにMATERIALIZED VIEW関連の権限を付与
GRANT CREATE TABLE TO mview_saki;
GRANT CREATE MATERIALIZED VIEW TO mview_saki;
GRANT ON COMMIT REFRESH TO mview_saki;

MVIEW_SAKIにマテリアライズドビューの作成

  • del_flg0のデータのみ取得するように作成
CREATE MATERIALIZED VIEW test 
REFRESH FAST ON COMMIT
AS
SELECT id,id2 FROM mview_moto.test WHERE del_flg=0;

確認

  • FAST REFRESH ON COMMITモードのマテリアライズドビューの作成が完了。
SQL>insert into mview_moto.test values(1,1,0);

1行が作成されました。

SQL>insert into mview_moto.test values(2,1,1);

1行が作成されました。

SQL>commit;

コミットが完了しました。

SQL>select * from mview_moto.test;

        ID        ID2    DEL_FLG
---------- ---------- ----------
         1          1          0
         2          1          1

SQL>select * from mview_saki.test;

        ID        ID2
---------- ----------
         1          1