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.

How To Install Oracle Client In Silent Mode Using Response File

Due to X-Window unavailability, installation of 12.2 Oracle client has to be done through silent mode with response file.

Subscribe to get access

Read more of this content when you subscribe today.

How to Install and deinstall Oracle Text in 12.1.0.2

This post demonstrates how to deinstall / install Oracle Text in 12.1.0.2. Before doing so, DBA should make sure and understand the component dependencies as below.

Subscribe to get access

Read more of this content when you subscribe today.

How to Install and deinstall Oracle Text in 11.2.0.4

This post demonstrates how to deinstall / install Oracle Text in 11.2.0.4. Before doing so, DBA should make sure and understand the component dependency as below.

Subscribe to get access

Read more of this content when you subscribe today.

When Do You Need Startup Upgrade For OJVM PSU/RU Patches

While applying Combo of OJVM and Database Release Update 19.9.0.0.201020, something comes to my mind that when and why “startup upgrade” is needed ? Is it possible to startup database in normal mode before running datapatch command for post-installation step?

Subscribe to get access

Read more of this content when you subscribe today.