一個資料倉儲資料重新整理的實現機制（三）
Procedure EDEN_REFRESH.REFRESH_MV
用於重新整理MV並紀錄重新整理資訊
[@more@]這是一個包(package)的一部分，附上包頭：
CREATE OR REPLACE PACKAGE EDEN_REFRESH IS
PROCEDURE REFRESH_MV(P_USER_NAME IN VARCHAR2,
P_MV_NAME IN VARCHAR2,
P_LOG_DIR IN VARCHAR2);
PROCEDURE REFRESH_TAB(P_USER_NAME IN VARCHAR2,
P_TAB_NAME IN VARCHAR2,
P_PROC_NAME IN VARCHAR2,
P_LOG_DIR IN VARCHAR2); --the proc to rebuild tab
END EDEN_REFRESH;
過程EDEN_REFRESH.REFRESH_MV的指令碼如下。注意重新整理的時候呼叫了dbms_refresh.make / dbms_refresh.refresh / dbms_refresh.destroy，之所以不呼叫package dbms_mview, 是為了實現重新整理失敗時能自動回滾。參見文件http://blog.itpub.net/post/334/15305 和 http://blog.itpub.net/post/334/15868
CREATE OR REPLACE PACKAGE BODY EDEN_REFRESH IS
PROCEDURE REFRESH_MV(P_USER_NAME IN VARCHAR2,
P_MV_NAME IN VARCHAR2,
P_LOG_DIR IN VARCHAR2) IS
V_USER_NAM EDEN_REFR_LOG.USER_NAM%TYPE := '';
V_OBJ_NAM EDEN_REFR_LOG.OBJ_NAM%TYPE := '';
V_STRT_DAT_TIM EDEN_REFR_LOG.STRT_DAT_TIM%TYPE := NULL;
V_END_DAT_TIM EDEN_REFR_LOG.END_DAT_TIM%TYPE := NULL;
V_REFR_SEQ_NB EDEN_REFR_LOG.REFR_SEQ_NB%TYPE := 0;
V_TOT_DRTN_TIM INTEGER := 0; -- time consumed in seconds
V_ROWS_BEF_REFR EDEN_REFR_LOG.ROWS_BEF_REFR%TYPE := 0;
V_ROWS_AFT_REFR EDEN_REFR_LOG.ROWS_AFT_REFR%TYPE := 0;
V_ERR_TXT EDEN_REFR_LOG.ERR_TXT%TYPE := '';
V_BEGIN EDEN_REFR_LOG.STRT_DAT_TIM%TYPE := NULL;
V_END EDEN_REFR_LOG.END_DAT_TIM%TYPE := NULL;
V_PRG_DRTN INTEGER := 0;
V_COUNT NUMBER;
V_FULL_OBJ_NAME VARCHAR2(50) := '';
MISS_MV EXCEPTION;
BEGIN
V_USER_NAM := LOWER(P_USER_NAME); --user and object...
V_OBJ_NAM := LOWER(P_MV_NAME);
V_FULL_OBJ_NAME := V_USER_NAM || '.' || V_OBJ_NAM;
SELECT SYSDATE INTO V_BEGIN FROM DUAL;
SELECT MAX(REFR_SEQ_NB) --max refresh num...
INTO V_REFR_SEQ_NB
FROM EDEN_REFR_LOG L
WHERE L.USER_NAM = V_USER_NAM
AND L.OBJ_NAM = V_OBJ_NAM;
IF V_REFR_SEQ_NB IS NULL THEN
V_REFR_SEQ_NB := 0;
END IF;
SELECT count(*)
INTO V_COUNT
FROM DBA_MVIEWS
WHERE OWNER = upper(V_USER_NAM)
AND MVIEW_NAME = upper(V_OBJ_NAM);
IF V_COUNT = 0 THEN
RAISE MISS_MV;
END IF;
SELECT count(*)
INTO V_COUNT
FROM user_refresh
WHERE rname = 'EDEN_GROUP';
IF V_COUNT = 1 THEN
dbms_refresh.destroy('EDEN_GROUP');
END IF;
--row num before refresh...
EXECUTE IMMEDIATE 'SELECT count(*) from ' || V_FULL_OBJ_NAME
INTO V_ROWS_BEF_REFR;
--start time...
SELECT SYSDATE INTO V_STRT_DAT_TIM FROM DUAL;
--THIS IS THE REAL JOB...
DBMS_REFRESH.make(name => 'EDEN_GROUP',
list => 'EDEN_DUMMY,' || V_FULL_OBJ_NAME,
next_date => sysdate,
interval => 'sysdate+1/24',
implicit_destroy => true);
DBMS_REFRESH.refresh('EDEN_GROUP');
COMMIT;
DBMS_REFRESH.destroy('EDEN_GROUP');
COMMIT;
--end time...
SELECT SYSDATE INTO V_END_DAT_TIM FROM DUAL;
--what,total time of the job...
EXECUTE IMMEDIATE 'SELECT count(*) from ' || V_FULL_OBJ_NAME
INTO V_ROWS_AFT_REFR;
V_TOT_DRTN_TIM := (V_END_DAT_TIM - V_STRT_DAT_TIM) * 3600 * 24;
--refresh successfully,and write log table...
INSERT INTO EDEN_REFR_LOG
(USER_NAM,
OBJ_NAM,
OBJ_TYPE,
REFR_SEQ_NB,
STRT_DAT_TIM,
END_DAT_TIM,
TOT_DRTN_TIM,
ROWS_BEF_REFR,
ROWS_AFT_REFR,
ERR_TXT,
STA_COD)
VALUES
(V_USER_NAM,
V_OBJ_NAM,
'V',
V_REFR_SEQ_NB + 1,
V_STRT_DAT_TIM,
V_END_DAT_TIM,
V_TOT_DRTN_TIM,
V_ROWS_BEF_REFR,
V_ROWS_AFT_REFR,
'',
'Y');
COMMIT;
SELECT SYSDATE INTO V_END FROM DUAL;
V_PRG_DRTN := (V_END - V_BEGIN) * 3600 * 24;
dbms_output.put_line('user name: ' || v_user_nam);
dbms_output.put_line('object name: ' || v_obj_nam);
dbms_output.put_line('object type: ' || 'V');
dbms_output.put_line('refresh seq No: ' ||
to_char(v_refr_seq_nb + 1));
dbms_output.put_line('refresh start: ' ||
TO_CHAR(v_strt_dat_tim, 'DD-MON-YYYY HH24:MI:SS'));
dbms_output.put_line('refrsh end: ' ||
TO_CHAR(v_end_dat_tim, 'DD-MON-YYYY HH24:MI:SS'));
dbms_output.put_line('refresh duration: ' || V_TOT_DRTN_TIM ||
' seconds');
dbms_output.put_line('program start: ' ||
TO_CHAR(V_BEGIN, 'DD-MON-YYYY HH24:MI:SS'));
dbms_output.put_line('program end: ' ||
TO_CHAR(V_END, 'DD-MON-YYYY HH24:MI:SS'));
dbms_output.put_line('program duration: ' || V_PRG_DRTN ||
' seconds');
dbms_output.put_line('rows before refresh: ' || v_rows_bef_refr);
dbms_output.put_line('rows after refresh : ' || v_rows_aft_refr);
dbms_output.put_line('error text: ' || '');
dbms_output.put_line('refresh status: ' || 'Y');
EXCEPTION
WHEN MISS_MV THEN
V_ERR_TXT := 'ORA-00942: materialized view does not exist';
BEGIN
INSERT INTO EDEN_REFR_LOG
(USER_NAM,
OBJ_NAM,
OBJ_TYPE,
REFR_SEQ_NB,
STRT_DAT_TIM,
END_DAT_TIM,
ERR_TXT,
STA_COD)
VALUES
(V_USER_NAM,
V_OBJ_NAM,
'V',
V_REFR_SEQ_NB + 1,
V_BEGIN,
SYSDATE,
V_ERR_TXT,
'N');
COMMIT;
dbms_output.put_line('user name: ' || v_user_nam);
dbms_output.put_line('object name: ' || v_obj_nam);
dbms_output.put_line('object type: ' || 'V');
dbms_output.put_line('refresh seq No: ' ||
to_char(v_refr_seq_nb + 1));
dbms_output.put_line('refresh start: ' ||
TO_CHAR(v_begin, 'DD-MON-YYYY HH24:MI:SS'));
dbms_output.put_line('refresh end: ' ||
TO_CHAR(sysdate, 'DD-MON-YYYY HH24:MI:SS'));
dbms_output.put_line('error text: ' || v_err_txt);
dbms_output.put_line('refresh status: ' || 'N');
EXCEPTION
WHEN OTHERS THEN
V_ERR_TXT := SQLERRM;
DBMS_OUTPUT.PUT_LINE(V_ERR_TXT); --write err msg to call env...
END;
WHEN OTHERS THEN
V_ERR_TXT := SQLERRM;
BEGIN
EXECUTE IMMEDIATE 'SELECT count(*) from ' || V_FULL_OBJ_NAME
INTO V_ROWS_AFT_REFR;
INSERT INTO EDEN_REFR_LOG
(USER_NAM,
OBJ_NAM,
OBJ_TYPE,
REFR_SEQ_NB,
STRT_DAT_TIM,
END_DAT_TIM,
TOT_DRTN_TIM,
ROWS_BEF_REFR,
ROWS_AFT_REFR,
ERR_TXT,
STA_COD)
VALUES
(V_USER_NAM,
V_OBJ_NAM,
'V',
V_REFR_SEQ_NB + 1,
V_STRT_DAT_TIM,
V_END_DAT_TIM,
V_TOT_DRTN_TIM,
V_ROWS_BEF_REFR,
V_ROWS_AFT_REFR,
V_ERR_TXT,
'N');
COMMIT;
SELECT SYSDATE INTO V_END FROM DUAL;
V_PRG_DRTN := (V_END - V_BEGIN) * 3600 * 24;
dbms_output.put_line('user name: ' || v_user_nam);
dbms_output.put_line('object name: ' || v_obj_nam);
dbms_output.put_line('object type: ' || 'V');
dbms_output.put_line('refresh seq No: ' ||
to_char(v_refr_seq_nb + 1));
dbms_output.put_line('refresh start: ' ||
TO_CHAR(v_strt_dat_tim,
'DD-MON-YYYY HH24:MI:SS'));
dbms_output.put_line('refrsh end: ' ||
TO_CHAR(v_end_dat_tim,
'DD-MON-YYYY HH24:MI:SS'));
dbms_output.put_line('refresh duration: ' || V_TOT_DRTN_TIM ||
' seconds');
dbms_output.put_line('program start: ' ||
TO_CHAR(V_BEGIN, 'DD-MON-YYYY HH24:MI:SS'));
dbms_output.put_line('program end: ' ||
TO_CHAR(V_END, 'DD-MON-YYYY HH24:MI:SS'));
dbms_output.put_line('program duration: ' || V_PRG_DRTN ||
' seconds');
dbms_output.put_line('rows before refresh: ' || v_rows_bef_refr);
dbms_output.put_line('rows after refresh : ' || v_rows_aft_refr);
dbms_output.put_line('error text: ' || v_err_txt);
dbms_output.put_line('refresh status: ' || 'N');
EXCEPTION
WHEN OTHERS THEN
V_ERR_TXT := SQLERRM;
DBMS_OUTPUT.PUT_LINE(V_ERR_TXT); --write err msg to call env...
END;
END REFRESH_MV;
執行方式如下：
SQL>exec eden_refresh.refresh_mv(schema_name,MV_name,log_path); 其實log_path這個引數沒用到，先不去刪它
SQL> exec eden_refresh.refresh_mv('edenfr','EDEN_RLF_INVC_MV','c:');
PL/SQL procedure successfully completed
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/207/viewspace-795298/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 一個資料倉儲資料重新整理的實現機制（一）
- 一個資料倉儲資料重新整理的實現機制（八）
- 一個資料倉儲資料重新整理的實現機制（七）
- 一個資料倉儲資料重新整理的實現機制（六）
- 一個資料倉儲資料重新整理的實現機制（五）
- 一個資料倉儲資料重新整理的實現機制（四）
- 一個資料倉儲資料重新整理的實現機制（二）
- 資料倉儲中的三種資料庫模型資料庫模型
- 如何用資料倉儲管理海量資料？直接訪問資料倉儲資料時的4個限制
- 設計資料倉儲和資料倉儲的粒度
- 資料庫倉庫系列：(一)什麼是資料倉儲，為什麼要資料倉儲資料庫
- 資料倉儲—資料倉儲—Sybase IQ 介紹
- 資料倉儲中的分析SQL——資料倉儲手冊SQL
- 資料倉儲
- 一個簡化、落地的實時資料倉儲解決方案
- Oracle資料倉儲的實時資料採集XSOracle
- 網站資料分析：資料倉儲相關的問題（三）網站
- 資料庫和資料倉儲資料庫
- 資料倉儲—資料倉儲—NCR Teradata Warehouse 介紹
- 資料倉儲之路
- 用Rust 實現的現代化實時開源資料倉儲Rust
- 資料倉儲和後設資料
- 資料倉儲、資料湖與湖倉一體的區別與聯絡
- hive資料倉儲匯入資料的方法Hive
- 資料湖 vs 資料倉儲 vs 資料庫資料庫
- 資料倉儲的組成
- 資料倉儲中的概念
- 資料湖是下一代資料倉儲？
- DW資料倉儲的一些概念
- 資料倉儲—資料倉儲—IBM DB2 Datawarehouse 介紹IBMDB2
- 萬字詳解資料倉儲、資料湖、資料中臺和湖倉一體
- 一個不錯的資料倉儲名字查詢網站網站
- 資料倉儲一般模式分析模式
- 資料湖會取代資料倉儲嗎?
- 談談資料湖和資料倉儲
- 淺談資料倉儲和大資料大資料
- 資料倉儲—資料庫—Oracle 介紹資料庫Oracle
- 資料倉儲 - ER模型模型