MV On demand refresh权限问题

MV_TEST在Schema1下创建，Schema2下无法进行刷新解决方法Instead of ALTER ANY MATERIALIZED VIEW try GRANT ALTER ON MV_TEST to Schema2：

ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2251
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2457
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2426
ORA-06512: at line 1

Schema1:

create materialized veiw mv_test refresh force on demand
as
select * from test;

CREATE OR REPLACE PROCEDURE REFRESH_MV_TEST_RECON
AS
BEGIN
DBMS_MVIEW.refresh(List =>'MV_TEST',Method =>'complete',refresh_after_errors =>True);
END;
/

GRANT EXECUTE ON REFRESH_MV_TEST_RECON TO Schema2;

GRANT SELECT ON MV_TEST TO Schema2;

 

Schema2:

BEGIN
Schema1.REFRESH_MV_TEST;
END;
/

SELECT * FROM MV_TEST;

如上即可达到刷新的效果。

------------------------------------------------Basic Knowledge--------------------
--全量刷新
BEGIN
DBMS_MVIEW.refresh(List =>'MV_TEST',Method =>'complete',refresh_after_errors =>True);
END;
/

--增量刷新
BEGIN
DBMS_MVIEW.refresh(List =>'MV_TEST',Method =>'fast',refresh_after_errors =>True);
END;
/

BEGIN
DBMS_MVIEW.refresh(List =>'MV_TEST',Method =>'force',refresh_after_errors =>True);
END;
/

create materialized veiw mv_test refresh force on demand
start with to_date('2022-06-30 10:00:00','yyyy-mm-dd hh24:mi:ss')
next to_date(concat(to_char(sysdate+1,'yyyy-mm-dd'),'10:00:00'),'yyyy-mm-dd hh24:mi:ss')
as
select * from test;

on demand:在用户需要刷新的时候刷新，这里就要求用户自己手动去刷新了（也可以使用JOB定时刷新）
on commit:当主表中有数据提交的时候，立即刷新MV中的数据；
start...:从指定的时间开始，每隔一段时间（由next指定)就刷新一次；