database

Oracle Mview教學

標籤： Oracle

Oracle Mview教學

數據庫之間可以通過DBLINK+Materialized View實現數據同步；若需要對物化視圖做UPDATE更新操作，則需要建立Writable Materialized View，在建立物化視圖時指定FOR UPDATE子句。由於Writable Materialized View要求物化視圖能夠快速刷新，所以建立必要的物化視圖日志Materialized View Log。

刷新方式：FAST/COMPLETE/FORCE
刷新時間：ON DEMAND/ON COMMIT
是否可更新：UPDATABLE/READ ONLY
是否支持查詢重寫：ENABLE QUERY REWRITE/DISABLE QUERY REWRITE

Refresh Type COMPLETE、FAST和FORCECOMPLETE: 每次refresh 時重新執行 mview Query且重新建置 mview (費時)(文件中提到如果是 complete，mview 的 pctfree 會是0，pctused 會是99，十分合理，因為這mview 每次都會重新建置，不需要留空間update使用)(不必建Mview log)
FAST: refresh mview 只更新 master 異動的資料 (較快但要建 mview log)
FORCE: refresh mview 時，會嘗試以 FAST 方式 refresh，如果無法完成則以 COMPLETE 方式refresh

Source端
使用scott/tiger這一組帳號測試建立MView LOG

Create materialized view log on MVIEW_DEPT with rowid including new values;

刪除Mview log
drop materialized view log on scott.dept;

查看MLOG的情况select log_owner, master, log_table from dba_mview_logs

Target 目標端

先建立DBLink
1.建立tnsname

2.建立User
create user user1 identified by user1 default tablespace users temporary tablespace temp account unlock;

3.授予權限
SQL> grant connect, create database link,create session,create synonym to user1;

4. 使用 user1 身份建立 DBLink
SQL> conn user1/user1
SQL> create database link c5_db3todb1 connect to scott identified by tiger using 'testdb1';

利用sys身份觀察已建立的 DBLink
sql> select OWNER,OBJECT_NAME,OBJECT_TYPE,STATUS from dba_objects where object_type='DATABASE LINK';

建立TNS:
TESTDB1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.55.79.26)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = testdb)
)
)
測試 user1 是否可以查詢遠端資料庫(testdb1)的 scott.dept 表格。

SQL> select * from scott.dept@C5_DB3TODB1

建立Mview，NEXT SYSDATE + 1/1440 每分鐘更新一次

CREATE MATERIALIZED VIEW Material_View_Name
[TABLESPACE TBS_XXX]REFRESH [ON DEMAND (預設)/ COMMIT] [COMPLETE / FORCE (預設) / FAST]
[WITH Primary Key (預設)/ ROWID / OBJECT ID]
[START WITH SYSDATE NEXT (SYSDATE+1)]
AS
SELECT SQL
–以上 SQL 的順序是有意義的, 必須注意.

Refresh Interval DEMAND: 分為自動跟手動，手動就是當需要refrest mview時執行dbms_mview.refresh; 自動就是在create mview 時指定排程時間，會增加一個job在 dba_jobs定時refresh
COMMIT: master table 資料有異動的時候，transaction commit時 refresh mview。 (即時同步)

grant create materialized view, create table to user1;

create materialized view MVIEW_DEPT refresh force with Primary Key START WITH sysdate NEXT SYSDATE + 1/1440 for update as select * from scott.dept@C5_DB3TODB1;

for update:代表MView是否updatable?沒有加代表no updatable.

查詢 job 的狀態
select job -- job id,
broken -- 是否停用(Y=停用,N=啟用),
what -- job 內容,
last_date -- 最新更新的時間,
next_date -- 下次更新的時間,
interval -- 更新頻率
from dba_jobs
where Log_user = 'owner' -- 帳號

附:刷新時間參數說明

30秒==> sysdate+30/(24*60*60)

1分鐘==> sysdate+1/(24*60) OR sysdate+60/(24*60*60)

5分鐘==> sysdate+5/(24*60)

30分鐘==>sysdate+30/(24*60)

60分鐘==>sysdate+60/(24*60)

1小時==>sysdate+1/24 OR sysdate+60/(24*60)

1天==>sysdate+1

1個月==>sysdate+30

每天早上9點==>TRUNC(SYSDATE)+1+9/24

要啟用 owner 全部的 job , 先用以下 SQL 輸出需要的 SQL 指令
select 'exec dbms_job.broken(' || job || ',false);'from dba_jobswhere Log_user = ' owner 'and broken = 'Y' -- 停用中的job

要停用 owner 全部的 job , 先用以下 SQL 輸出需要的 SQL 指令
select 'exec dbms_job.broken(' || job || ',true);'from dba_jobswhere Log_user = ' owner 'and broken = 'N' -- 啟用中的job

刪除Mview:
SQL>drop materialized view mview_dept;

手動暫停更新MView資料
ALTER MATERIALIZED VIEW MVIEW_DEPT refresh on demand;

重新啟動自動更新MView資料
ALTER MATERIALIZED VIEW MVIEW_DEPT refresh start WITH sysdate NEXT SYSDATE + 1/1440;

手動更新，並使用complete更新物件
exec dbms_mview.refresh('MVIEW_DEPT','C',atomic=>false);
atomic=>false：Truncate table，用在大量資料底下

批次更新dbms_refresh.refresh()atomic_refresh:=false所有MView各自獨立成一個Transaction，完成一個即commit。各個Mview更新時則按照前述規則。更新失敗時其他MView不受影響。
做法：先truncate掉原本mview中的資料，再全部新增。因truncate無法倒回，更新失敗時則Mview為空。

atomic_refresh:=true所有MView在同一個Transaction，全部完成才commit。各個Mview更新時則按照前述規則，更新失敗時全部rollback。
因delete會把刪掉的資料置入redo segment，若資料量大則更新非常花時間，rollback時須把資料再搬回，更花時間。
做法：先Delete Mview中所有資料，再全部新增，因delete掉資料，所以可以rollback。

手動更新，並使用FAST更新物件
exec dbms_mview.refresh('MVIEW_DEPT','F');

查詢Mview log 更新時間：
SELECT * FROM DBA_BASE_TABLE_MVIEWS order by mview_last_refresh_time;

更新時間為2011/9/13，代表的是這一些Mview沒有使用到
查看Mview log Size&沒有使用到的Mview:

select log_owner,master,log_table,current_snapshots,owner,segment_name,segment_type,tablespace_name,bytes/1024/1024 from DBA_SNAPSHOT_LOGS T , DBA_SEGMENTS D WHERE T.LOG_TABLE=D.SEGMENT_NAME ORDER BY bytes desc;

從此sql可以看到前二大Mview log分別為8G及1.9g，要再確認此Mview是否都沒有使用到，若沒有使用到，即可把它給刪除掉。

建置過程中問題：

SQL> grant create table to user1;
Grant succeeded.

ORA-01950: no privileges on tablespace XXX
SQL> alter user user1 quota 100m on users;
User altered.
SQL> grant unlimited tablespace to user1;
Grant succeeded.

參考文件：
http://rritw.com/a/bianchengyuyan/C__/20130322/327792.html
http://py3939.pixnet.net/blog/post/25399529
http://blog.roodo.com/fionscenery/archives/18994542.html

Oracle Mview教學

Read next