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 -- //www.freelists.org/webpage/oracle-l