Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Refresh materialized view by other user then owner

Refresh materialized view by other user then owner

From: varciasz <varciasz_at_gmail.com>
Date: Sun, 30 Apr 2006 13:40:13 +0200
Message-ID: <4454a0fc.76341dcf.2a9e.712c@mx.gmail.com>


Hello,  

I'm trying to refresh materialized view (Oracle 9i) and I have error

that some privileges are needed but I don't 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

--

http://www.freelists.org/webpage/oracle-l Received on Sun Apr 30 2006 - 06:40:13 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US