CODEWITHSUNDEEP
oracle-databasematerialized-views
user2589193
user2589193

Reputation: 69

Materialized view for different oracle schema results in ORA-1208

I want to create on commit materialized oracle view pointing to table from different schema within same database instance.

schema (BATCH): 

CREATE TABLE "BATCH"."BATCH_CONFIG" 
   (    "KEY" VARCHAR2(100 BYTE), 
    "VALUE" VARCHAR2(4000 BYTE), 
    "COMMENTS" VARCHAR2(4000 BYTE), 
    "UPDATED_BY" VARCHAR2(25 BYTE), 
    "UPDATED" TIMESTAMP (6), 
     CONSTRAINT "PK_BATCH_CONFIG" PRIMARY KEY ("KEY")
   );

create materialized view log on batch_config;
grant all on batch_config to profile;

schema (PROFILE):

create materialized view mv_batch_config REFRESH FAST ON COMMIT
        as select * from BATCH.batch_config;

Got Error:

SQL Error: ORA-12018: following error encountered during code generation for "PROFILE"."MV_BATCH_CONFIG"
ORA-00942: table or view does not exist
12018. 0000 -  "following error encountered during code generation for \"%s\".\"%s\""
*Cause:    The refresh operations for the indicated materialized view could
           not be regenerated due to errors.
*Action:   Correct the problem indicated in the following error messages and
           repeat the operation.

What is the issue with ? I am able to view query by (from profile schema) select * from BATCH.batch_config

Upvotes: 1

Views: 11034

Answers (1)

user2589193
user2589193

Reputation: 69

OK finally I got the solution.

I have to grant select on mview log also.

GRANT SELECT ON MLOG$_BATCH_CONFIG TO PROFILE;

Upvotes: 2

Related Questions