mvref.sql
code:SQL
set serveroutput on
set trimspool on
set verify off
set feedback off
set timing off
set lines 200
whenever sqlerror exit 100
spool &4
DECLARE
mvname varchar2(100) := '&1';
reftype char(1) := upper('&2');
grantor varchar2(50) := upper('&3');
err exception;
BEGIN
-- C 完全リフレッシュ
IF reftype = 'C' THEN
DBMS_OUTPUT.PUT_LINE('======== mvname ||'の完全リフレッシュを実行します');
-- C-1 MVの参照権限を参照スキーマから剥奪
DECLARE
sql_stmt1 varchar2(100);
vcode number;
BEGIN
sql_stmt1 := 'revoke select on '|| mvname ||' from '|| grantor;
DBMS_OUTPUT.PUT_LINE(systimestamp||' 権限剥奪開始');
EXECUTE IMMEDIATE sql_stmt1;
DBMS_OUTPUT.PUT_LINE(systimestamp||' 権限剥奪完了');
EXCEPTION
WHEN others THEN
vcode := sqlcode;
-- (再実行時等)既に権限が剥奪されていればエラーとせず処理を継続
IF vcode = -1927 THEN
DBMS_OUTPUT.PUT_LINE(systimestamp||' 処理を継続します');
-- 該当するMViewがディクショナリに存在しない場合はエラーとして上げる
ELSIF vcode = -942 THEN
DBMS_OUTPUT.PUT_LINE(systimestamp||' MView名(第1引数)が違います');
RAISE err;
-- その他のORA-エラーはエラーとして上げる
ELSE
DBMS_OUTPUT.PUT_LINE(systimestamp||' '||sqlerrm);
RAISE;
END IF;
END;
-- C-2 完全リフレッシュ実行
BEGIN
DBMS_OUTPUT.PUT_LINE(systimestamp||' 完全リフレッシュ開始');
DBMS_MVIEW.REFRESH(mvname, method => 'C', atomic_refresh=>false);
DBMS_OUTPUT.PUT_LINE(systimestamp||' 完全リフレッシュ完了');
EXCEPTION
WHEN others THEN
DBMS_OUTPUT.PUT_LINE(systimestamp||' '||sqlerrm);
RAISE;
END;
-- C-3 MVの参照権限を参照スキーマに付与
DECLARE
sql_stmt2 varchar2(100);
vcode number;
BEGIN
sql_stmt2 := 'grant select on ' || mvname ||' to '|| grantor;
DBMS_OUTPUT.PUT_LINE(systimestamp||' 権限付与開始');
EXECUTE IMMEDIATE sql_stmt2;
DBMS_OUTPUT.PUT_LINE(systimestamp||' 権限付与完了');
EXCEPTION
WHEN others THEN
RAISE;
END;
-- F 高速リフレッシュ
ELSIF reftype = 'F' THEN
DBMS_OUTPUT.PUT_LINE('======== mvname ||'の高速リフレッシュを実行します');
DECLARE
vcode number;
BEGIN
DBMS_OUTPUT.PUT_LINE(systimestamp||' 高速リフレッシュ開始');
DBMS_MVIEW.REFRESH(mvname, method => 'F');
DBMS_OUTPUT.PUT_LINE(systimestamp||' 高速リフレッシュ完了');
EXCEPTION
WHEN others THEN
vcode := sqlcode;
DBMS_OUTPUT.PUT_LINE(systimestamp||' '||sqlerrm);
-- 元表にMViewLogが作成されておらず、高速リフレッシュが実行不可の場合
IF vcode = -12004 THEN
DBMS_OUTPUT.PUT_LINE('======== Refresh Type='||reftype||':第2引数(リフレッシュ・タイプ)にはCを指定してください'); RAISE err;
ELSIF vcode = -20000 THEN
DBMS_OUTPUT.PUT_LINE(systimestamp||' MView名(第1引数)が違います');
RAISE err;
ELSE
RAISE;
END IF;
END;
ELSE
DBMS_OUTPUT.PUT_LINE('======== Refresh Type='||reftype||':第2引数(リフレッシュ・タイプ)にはCまたはFを指定してください'); RAISE err;
END IF;
EXCEPTION
WHEN err THEN
DBMS_OUTPUT.PUT_LINE('======== 問題を修正し再実行してください');
RAISE;
END;
/
spool off
set feedback on
set timing on
set lines 80
undefine 1 2 3
exit 0