ORA-00942 or ORA-01031 error occurs while creating a materialized view from a table of another schema.
Source Table : USERA.TABLE_A
Materialized View : USERB.TABLE_A_MV
SQL> show user
USER is "USERB"
SQL> create materialized view TABLE_A_MV
BUILD IMMEDIATE
REFRESH FAST ON commit
with primary key
as select * from usera.table_a ;
as select * from usera.table_a
*
ERROR at line 5:
ORA-12018: following error encountered during code generation for
"USERB"."TABLE_A_MV"
ORA-00942: table or view does not exist
But USERB has no problem to access USERA.TABLE_A:
SQL> show user
USER is "USERB"
SQL> select count(*) from usera.table_a;
COUNT(*)
----------
1623583
If creating the materialized view by sys user, then gets another different error:
SQL> show user
USER is "SYS"
SQL> create materialized view USERB.TABLE_A_MV
BUILD IMMEDIATE
REFRESH FAST ON commit
with primary key
as select * from usera.table_a ;
as select * from usera.table_a
*
ERROR at line 4:
ORA-01031: insufficient privileges
Subscribe to get access
Read more of this content when you subscribe today.
Make DBA Life Easy 