RE: Refresh materialized view by other user then owner

Thanks for trying but this still doesn't work at all.


>>You're creating the materialized view in schema SYSTEM, but try to refresh
>>an mview in schema SYS - that cannot work.

At first - all tables, MViews etc when I'm logged in as "SYSTEM" appears in
SYS schema. That's why I used "SYS." instead "SYSTEM." I don't know how its
work but it is like that.

So after : 
        EXEC dbms_mview.refresh('SYSTEM.V1 ');

I have error from Oracle:
          *
         ERROR at line 1:
         ORA-23401: materialized view "SYSTEM"."V1" does not exist
         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


And after 
        EXEC dbms_mview.refresh('sys.V1 ');

I have the same old 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





It looks like that this is not so simple ...

  


________________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Stefan Knecht
Sent: Sunday, April 30, 2006 11:00 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: Re: Refresh materialized view by other user then owner


From what I can see from your posting:


    begin
        DBMS_MVIEW.REFRESH('sys.My_View','c'); 
    end;
You're creating the materialized view in schema SYSTEM, but try to refresh
an mview in schema SYS - that cannot work. 

The error message you're getting doesn't seem to be accurate, though, you
should be getting ora-23401 if that is the cause - I only tested on 10gr2,
so 9i might react differently but I cannot test this right now. 

system@CENTRAL> grant create session, alter any materialized view to user1
identified by user1;

Grant succeeded.

system@CENTRAL> create table t1 (x int primary key);

Table created.

system@CENTRAL> create materialized view v1 as select * from t1
  2  ;

Materialized view created.

system@CENTRAL> @conn user1/user1
Connected.
user1@CENTRAL> exec dbms_mview.refresh('SYSTEM.V1 ');

PL/SQL procedure successfully completed.

user1@CENTRAL> 


Either way, you really shouldn't be using SYS or SYSTEM for userdata.

Stefan 




--
http://www.freelists.org/webpage/oracle-l

Other related posts: