Hello,

I have problem. I'm trying to refresh materialized view and I have
error that some pirivileges are needed but I dont have idea what more
can be needed

I'm trying to execute from User2:

begin
DBMS_MVIEW.REFRESH('sys.My_View','c');
end;

and Oracle gives me back error:

begin
*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 794
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 851
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 832
ORA-06512: at line 2


Table, materialized view and privileges are made in this way:

create or replace table My_Table (aa integer primary key);
create materialized view My_View as select * from My_Table ;

CREATE USER User2;
IDENTIFIED BY ThisIsMySecretPassword;
GRANT ALL ON My_View TO User2;
GRANT SELECT ON My_Table TO User2;
GRANT CREATE SESSION TO User2;
GRANT ALTER ANY MATERIALIZED VIEW to User2;
GRANT SELECT ANY TABLE to User2;
GRANT CREATE ANY MATERIALIZED VIEW to User2
GRANT DROP ANY MATERIALIZED VIEW to User2


--END

This table and materialized view have been made by user System

I also try to give the same Roles that have System but this is still
the same error.
Refresh works fine executed by owner of this View

What privileges are missing?

Thanks for any help

varciasz

varciasz (AT) gmail (DOT) com wrote:
Should I understand from your example that you built a materialized
view as SYS? What schema are you in when you execute the REFRESH?
Does that schema have EXECUTE on DBMS_MVIEW? Granted how?

Daniel Morgan
www.psoug.org