Database Software

Hi all.

I'm running 10.2.0.4 Standard on a 2K3 server - and the issue I'm seeing is also observed under 10.2.0.1 XE on XP. Last time I worked with MVs was replicating 8i to 9i.

My issue here is that I cannot configure fast refresh without granting excessive permissions (DBA) to the schema owner.

I have 2 schemas in the same database. I want to configure replication of a table in SCHEMA1, such that the MV for it is in SCHEMA2. ((for background, this is a development environment which is intended to replicate production as closely as possible - production will have these two schemas in separate databases on separate servers. We elected to use MVs for performance reasons)).

So. My reading of the Oracle dox indicates that 'SCHEMA2' should have CREATE MATERIALIZED VIEW, and CREATE TABLE or CREATE ANY TABLE system privelege.

My process is:

SYS: grant create materialized view to SCHEMA2;
SYS: grant select on SCHEMA1.TABLE1 to SCHEMA2;
SCHEMA1: create materialized view log on SCHEMA1.TABLE1 noparallel;
SCHEMA2: create materialized view MV_TABLE1 refresh fast on demand as select * from SCHEMA1.TABLE1;

ERROR at line 1:
ORA-12018: following error encountered during code generation for
"SCHEMA2"."MV_TABLE1"
ORA-00942: table or view does not exist

***I had read about a bug wherein you can't grant fast refresh right away, but have to alter the MV after creation. So:

SCHEMA2: create materialized view MV_TABLE1 as select * from SCHEMA1.TABLE1;
Materialized view created.

SCHEMA2: alter materialized view MV_TABLE1 refresh fast on demand;
Materialized view altered.

***Looks good - so let's try to refresh it...

SCHEMA2: exec dbms_mview.refresh('MV_TABLE1', 'c');
PL/SQL procedure successfully completed.

SCHEMA2: exec dbms_mview.refresh('MV_TABLE1', 'f');
BEGIN dbms_mview.refresh('MV_TABLE1', 'f'); END;

*
ERROR at line 1:
ORA-12018: following error encountered during code generation for
"SCHEMA2"."'MV_TABLE1'"
ORA-00942: table or view does not exist
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2255
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2461
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2430
ORA-06512: at line 1

*** So I can do a complete refresh, but not a fast one.
*** I tried granting CREATE ANY TABLE to SCHEMA2, and when I do that and try a fast refresh, I get

ERROR at line 1:
ORA-12018: following error encountered during code generation for
"SCHEMA2"."'MV_TABLE1'
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2255
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2461
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2430
ORA-06512: at line 1

If I grant DBA to SCHEMA2, I can refresh with the 'fast' parameter, without any errors.

*** So my question would be: What are the permissions required in order to do a fast refresh? What am I missing here???

Thanks kindly for any feedback!

DW