Issue running DBMS_MVIEW.REFRESH

From: Paul <paulwragg2323_at_hotmail.com>
Date: 14 Oct 2005 06:17:05 -0700
Message-ID: <1129295825.604233.239020@g44g2000cwa.googlegroups.com>

I have created a materialized view but I am having issues executing the DBMS_MVIEW.REFRESH procedure against it as a user who does not own the view.

Here are the details:

(logged in as SYS)

DEFINE &USER1 = USER1
DEFINE &USER2 = USER2 CREATE USER &USER1 ...
CREATE USER &USER2... GRANT CREATE SESSION TO &USER1;
GRANT CONNECT TO &USER1;
GRANT CREATE TRIGGER TO &USER1; *
GRANT CREATE PROCEDURE TO &USER1; *

GRANT SELECT ON DBA_OBJECTS TO &USER1; *
GRANT SELECT ON DBA_SYNONYMS TO &USER1;*
GRANT SELECT ON ALL_OBJECTS TO &USER1;*

GRANT CREATE MATERIALIZED VIEW TO &USER1; GRANT CREATE SESSION TO &USER2;
GRANT CONNECT TO &USER2;
GRANT ALTER ANY TABLE TO &USER2;*
GRANT SELECT ON DBA_SYNONYMS TO &USER2;* GRANT SELECT ON ALL_OBJECTS TO &USER2;*
GRANT EXECUTE ON DBMS_MVIEW TO &USER2;

I don't think these are relevant but have included to try to include as much info as possible

(Logged in as &USER1)

CREATE MATERIALIZED VIEW TEST1
AS ...

GRANT SELECT, INSERT, UPDATE, DELETE ON TEST1 TO &USER2;
(logged in as &USER2)

CREATE SYNONYM TEST1 FOR &USER1..TEST1; Then executing the following statement:

EXECUTE DBMS_MVIEW.REFRESH('TEST1'); fails with the error:

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 1

Does anybody know whether or not I can do this and if so what additional privileges are required?

Thanks in advance

Paul Received on Fri Oct 14 2005 - 08:17:05 CDT