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_at_CENTRAL> grant create session, alter any materialized view to user1 identified by user1;

Grant succeeded.

system_at_CENTRAL> create table t1 (x int primary key);

Table created.

system_at_CENTRAL> create materialized view v1 as select * from t1   2 ;

Materialized view created.

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

PL/SQL procedure successfully completed.

user1_at_CENTRAL>

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

Stefan

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