Oracle Mview教學

Oracle Mview教學

標籤: 

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=>falseTruncate 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分別為8G1.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 

Read more

How to migrate Raspberry Pi 5 OS from micro SD to NVME m.2 SSD

首先我買了Raspberry Pi CM5後來買了Raspberry Pi CM5 I/O board來當個人電腦使用,系統是安裝在256GB SD卡上運行的很好。用久了在開啟較肥的程式像Web Browser或LiberOffice會有慢半拍的反應,而有了升級NVME m.2 SSD念頭。 因為Raspberry Pi 5支援的最快PCIe gen3 x 4就不去考慮快的Gen4 or Gen5 m.2 SSD。找了ADATA出的 LEGEND 710入門級的產品,會利用HMB(Host Memory Buffer)來加速I/O速度,因為是Raspberry Pi OS kernel會認不得而無法正常使用 事先在SD卡的/boot/firmware/cmdline.txt 加入 kernel command line參數如下,然後重開機m.

By Phillips Hsieh

How to document Home Lab and Network

運維機房和跨域的網路,會遇到各式需求與問題,用對工具才能分析問題,個人覺得最重要的是使用能處理問題的工具。 推薦目前想學和正在使用的平台與軟體,協助將公司/家用機房文件化 佈告欄任務管理 Focalboard 白板可管理任務指派 網路架構文件編寫 netbox 精細管理網路設備與連接線路 IP 資源管理 phpipam 專注網路IP分配 邏輯塊文件編寫 draw.io 視覺化概念圖 機房設備管理 ITDB 管理設備生命週期與使用者

By Phillips Hsieh

如何在Raspberry Pi4上安裝Proxmox for ARM64

第一步 準備好Raspberry Pi 4 / CM4 4GB RAM,這裡要留意CM4如果是買有內建eMMC storage會限制不能使用SD卡開機而限制本地空間容量,如果沒有NAS外接空間或使用USB開機的話,建議買CM4 Lite插上大容量SD卡 第二步 去Armbian官網下載最小化Debian bookworm image https://www.armbian.com/rpi4b/ Armbian 25.2.2 Bookworm Minimal / IOT 然後寫入SD/USB開機碟,寫入方法參考官方文件 https://github.com/raspberrypi/usbboot/blob/master/Readme.md Note: 官方提供的預先設定系統方法,可以在Armbian初次啟動自動化完成系統設定。連結在此 https://docs.armbian.com/User-Guide_Autoconfig/

By Phillips Hsieh

世界越快心越慢

在晚飯後的休息時間,我特別享受在客廳瀏灠youtube上各樣各式創作者的影音作品。很大不同於傳統媒體,節目多是針對大多數族群喜好挑選的,在youtube上我會依心情看無腦的動畫、一些旅拍記錄、新聞時事談論。 尤其在看了大量的Youtube的分享後,我真的感受到會限制我的是我的無知,特別是那些我想都沒想過的實際應用,在學習後大大幫助到我的生活和工作層面。 休息在家時,我喜歡想一些沒做過的菜,動手去設計生活和工作上的解決方案,自己是真的很難閒著沒事做。 如創作文章,陪養新的習慣都能感覺到成長的喜悅,是不同於吃喝玩樂的快樂的。 創作不去限制固定的形式,文字是創作、影像聲音也是創作,記錄生活也是創作,我想留下的就是創造—》實現—》回憶,這樣子的循環過程,在留下的足跡面看到自己一路上的成長、失敗、絕望、重新再來。 雖然大部份的時候去做這些創作也不明白有什麼特別的意義,但不去做也不會留下什麼,所以呀不如反事都去試試看,也許能有不一樣的水花也許有意想不到的結果,投資自己永遠不會是失敗的決定,不是嗎?先問問自己再開始計畫下一步,未來沒人說得準。 像最近看youtube仍大一群人在為DOS開

By Phillips Hsieh