ORA-39083: Object type MATERIALIZED_VIEW:”KISH”.”MVREMOTE” failed to create with error:ORA-00942: table or view does not exist
Cause:
The error is expected behavior because of missing dependent container table of the mview.
[oracle@xhydra ~]$ impdp \'/ as sysdba\' directory=home dumpfile=mview.dmp logfile=mview.log remap_schema=KISH:KISH
Import: Release 19.0.0.0.0 - Production on Mon Jun 12 20:02:24 2023
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Password:
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Warning: Oracle Data Pump operations are not typically needed when connected to the root or seed of a container database.
Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_FULL_01": "/******** AS SYSDBA" directory=home dumpfile=mview.dmp logfile=mview.log remap_schema=KISH:KISH
Processing object type SCHEMA_EXPORT/MATERIALIZED_VIEW
ORA-39083: Object type MATERIALIZED_VIEW:"KISH"."MVREMOTE" failed to create with error:
ORA-00942: table or view does not exist
Failing sql is:
CREATE MATERIALIZED VIEW "KISH"."MVREMOTE" ("XNAME", "XID", "CITY", "COUNTRY", "DEPARTMENT", "DEP_ID", "XDATE", "SALARY") USING ("MVREMOTE", (10, 'DB9ZX', 1, 0, 0, "KISH", "YTBL", '2023-06-12 19:32:24', 262144, 73213, '1950-01-01 12:00:00', '', 1, 'FE01', 15319225, 0, NULL, (1, "XID", "XID", 2, 323, 0)), 2097249, 10, ('1950-01-01 12:00:00', 61, 0, 0, 15319225, 0, 0, 4194304, 2, NULL, NULL)) REFRESH COMPLETE WITH PRIMARY KEY AS SELECT "YTBL"."XNAME" "XNAME","YTBL"."XID" "XID","YTBL"."CITY" "CITY","YTBL"."COUNTRY" "COUNTRY","YTBL"."DEPARTMENT" "DEPARTMENT","YTBL"."DEP_ID" "DEP_ID","YTBL"."XDATE" "XDATE","YTBL"."SALARY" "SALARY" FROM "KISH"."YTBL" "YTBL"
All mviews are created along with a container table during initial phase. During export/import, the dependent container table should also be export/imported in database.
Solution:
Verify the container table.
SQL> col OWNER for a20
SQL> SELECT owner,object_name, object_type FROM dba_objects WHERE object_name = 'MVREMOTE';
OWNER OBJECT_NAME OBJECT_TYPE
-------------------- -------------------- --------------------
KISH MVREMOTE TABLE
KISH MVREMOTE MATERIALIZED VIEW
Export both container and its mview pair.
[oracle@xhydra ~]$ expdp \'/ as sysdba\' directory=home dumpfile=mview.dmp logfile=mview.log schemas=KISH include=TABLE:"IN('MVREMOTE')", MATERIALIZED_VIEW:"IN('MVREMOTE')" reuse_dumpfiles=Y
Export: Release 19.0.0.0.0 - Production on Mon Jun 12 20:24:30 2023
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Password:
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Warning: Oracle Data Pump operations are not typically needed when connected to the root or seed of a container database.
Starting "SYS"."SYS_EXPORT_SCHEMA_01": "/******** AS SYSDBA" directory=home dumpfile=mview.dmp logfile=mview.log schemas=KISH include=TABLE:IN('MVREMOTE'), MATERIALIZED_VIEW:IN('MVREMOTE') reuse_dumpfiles=Y
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/MATERIALIZED_VIEW
. . exported "KISH"."MVREMOTE" 277.0 MB 5000000 rows
Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
/home/oracle/mview.dmp
Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at Mon Jun 12 20:24:57 2023 elapsed 0 00:00:26
Import both container and mview.
[oracle@xhydra ~]$ impdp \'/ as sysdba\' directory=home dumpfile=mview.dmp logfile=mview.log INCLUDE=TABLE:"IN('MVREMOTE')", MATERIALIZED_VIEW:"IN('MVREMOTE')" table_exists_action=replace
Import: Release 19.0.0.0.0 - Production on Mon Jun 12 20:29:28 2023
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Password:
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Warning: Oracle Data Pump operations are not typically needed when connected to the root or seed of a container database.
Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_FULL_01": "/******** AS SYSDBA" directory=home dumpfile=mview.dmp logfile=mview.log INCLUDE=TABLE:IN('MVREMOTE'), MATERIALIZED_VIEW:IN('MVREMOTE') table_exists_action=replace
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "KISH"."MVREMOTE" 277.0 MB 5000000 rows
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/MATERIALIZED_VIEW
Job "SYS"."SYS_IMPORT_FULL_01" successfully completed at Mon Jun 12 20:29:35 2023 elapsed 0 00:00:06