ORA-00942: table or view does not exist ORA-01031: insufficient privileges while creating materialized view

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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.