Re: Refresh materialized view by other user then owner

Ahh okay, then there is one part of the confusion we can identify:

SYS is magical, SYS is different.If you connect AS SYSDBA, using any user, you get into the database as user SYS, and not the actual user you specified when connecting. And I tend to think that this is the root cause of your problems. You really, really, really should not use SYS for storing user data. The same goes for the SYSTEM tablespace, this is reserved for Oracle database internal data as well.

Could you try issuing the following statements, when you're connected to the database AS SYSDBA and let me know if this works.

• we'll first create a tablespace to store the table / mview
• here you just have to replace the path to a valid folder on your computer,
• I'm assuming you're running Windows, please correct me if I'm wrong create tablespace users datafile 'c:\users.dbf' size 10M;
• we'll create a user that will own the table and materialized view create user user_a identified by user_a quota unlimited on users;
• grant him the privileges necessary for this test grant create session, create table, create materialized view to user_a;
• create the user that will be able to refresh user_a's mview create user user_b identified by user_b;

grant create session, alter any materialized view to user_b;

• now connect as user_a to create the objects connect user_a/user_a
• connected as user_a we create the table and the mview create table a_table (x int primary key); create materialized view a_mview as select * from a_table;
• connect as user_b and refresh it connect user_b/user_b begin dbms_mview.refresh('USER_A.A_MVIEW','c'); end; /

Stefan

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