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

Other related posts: