Thanks, this example after few correction works OK. I really don't know what's wrong with schema SYS .
Below full, working script. All changes: You forget to put Table and MView to created Tablespace, It was also needed to give quota unlimited on SYSTEM; to user_a. I don't know why and what for this is needed but without it Oracle shows that I don't have privileges on tablespace SYSTEM
Connect system/***@(protected) AS SYSDBA
-- 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 My_Tablespace datafile 'c:\My_Tablespace.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 My_Tablespace quota unlimited on SYSTEM;
-- 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) tablespace My_Tablespace; create materialized view a_mview tablespace My_Tablespace 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; /
