Link to home
Start Free TrialLog in
Avatar of dsacker
dsackerFlag for United States of America

asked on

Materialized View Refresh Privilege

Anyone know why the following ...

SQL> call dbms_mview.refresh('MV_MYVIEW','c')

... would return the following?

SQL> ORA-01031: insufficient privileges
SQL> ORA-06512: at "SYS.DBMS_SNAPSHOT", line 803
SQL> ORA-06512: at "SYS.DBMS_SNAPSHOT", line 860
SQL> ORA-06512: at "SYS.DBMS_SNAPSHOT", line 841
SQL> ORA-06512: at line 1

***  Insert, delete, select and update have been granted properly to the user who gets the error. ***

The command to create the materialized view was:

CREATE MATERIALIZED VIEW MV_MYVIEW
TABLESPACE OURTABLESPACE_01
NOCACHE LOGGING NOPARALLEL
REFRESH COMPLETE ON DEMAND
AS
SELECT T1.FIELD1, T1.FIELD2, T2.FIELD3, T2.FIELD4, T2.FIELD5
FROM TABLE1 T1
     JOIN
     TABLE2 T2
     ON
     T1.KEYFIELD = T2.KEYFIELD;
ASKER CERTIFIED SOLUTION
Avatar of pettmans
pettmans
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of dsacker

ASKER

I think the following did the trick:

    grant alter any snapshot to <other_userid>;

Thanks.
Avatar of dsacker

ASKER

I was on 9.2.0.5
Avatar of xjwei_no1
xjwei_no1

grant create any Materialized View  to <user>