EXPORT/IMPORT A MATERIALIZED VIEW
In this article, mview is exported from source and imported back to same source for demonstration and should not be performed in production.
Check the existing mview to be exported.
SQL> col OWNER for a20
SQL> col MVIEW_NAME for a20
SQL> SELECT OWNER,MVIEW_NAME,STALENESS,STALE_SINCE from dba_mviews WHERE MVIEW_NAME='MVREMOTE';
OWNER MVIEW_NAME STALENESS STALE_SIN
-------------------- -------------------- ------------------- ---------
KISH MVREMOTE FRESH
Can we export a materialized view using expdp ? Yes, it can be achieved by “INCLUDE” option.
SQL> conn kish/password
Connected.
SQL>
SQL>
SQL> CREATE MATERIALIZED VIEW mvremote BUILD IMMEDIATE REFRESH COMPLETE ON DEMAND AS SELECT * FROM KISH.YTBL;
Materialized view created.
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
[oracle@xhydra ~]$ expdp \'/ as sysdba\' directory=home dumpfile=mview.dmp logfile=mview.log schemas=KISH include=MATERIALIZED_VIEW
Export: Release 19.0.0.0.0 - Production on Mon Jun 12 19:33:06 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=MATERIALIZED_VIEW
Processing object type SCHEMA_EXPORT/MATERIALIZED_VIEW
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 19:34:37 2023 elapsed 0 00:01:25
Also it is always advisable to export mview along with its container table as well to avoid any import errors with,
ORA-39083: Object type MATERIALIZED_VIEW:”KISH”.”MVREMOTE” failed to create with error:
ORA-00942: table or view does not exist
[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
To identify the container table, use the below query.
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
To import a materialized view, use the below steps.
[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
After importing the mview newly in the database, the staleness of mview becomes IMPORT because of the incomplete refresh. So a complete refresh need to be performed in order to synchronize the status to FRESH.
SQL> col OWNER for a20
SQL> col MVIEW_NAME for a20
SQL> SELECT OWNER,MVIEW_NAME,STALENESS,STALE_SINCE from dba_mviews WHERE MVIEW_NAME='MVREMOTE';
OWNER MVIEW_NAME STALENESS STALE_SIN
-------------------- -------------------- ------------------- ---------
KISH MVREMOTE IMPORT <===
Also, do we need to reregister the mview after import? No, since the container table is also imported with mview which are referenced objects, there is no need to register the mviews after import.
SQL> col NAME for a20
SQL> col OWNER for a20
SQL> col QUERY_TXT for a20
SQL> col MVIEW_SITE for a20
SQL> col VERSION for a20
SQL> set lines 200 pages 1000
SQL> select * from dba_registered_mviews where name ='MVREMOTE';
OWNER NAME MVIEW_SITE CAN UPD REFRESH_MET MVIEW_ID VERSION QUERY_TXT
-------------------- -------------------- -------------------- --- --- ----------- ---------- -------------------- --------------------
KISH MVREMOTE EXHYDRA YES NO PRIMARY KEY 21 ORACLE 8 MATERIALIZE SELECT "YTBL"."XNAME
D VIEW " "XNAME","YTBL"."XI
D" "XID","YTBL"."CIT
Y" "CITY","YTBL"."CO
KISH MVREMOTE DB9ZX YES NO PRIMARY KEY 81 ORACLE 8 MATERIALIZE SELECT "YTBL"."XNAME
D VIEW " "XNAME","YTBL"."XI
D" "XID","YTBL"."CIT
Y" "CITY","YTBL"."COAll the registered mviews details are stored in internal table SYS.REG_SNAP$.
SQL> SELECT * from SYS.REG_SNAP$;
SOWNER
--------------------------------------------------------------------------------------------------------------------------------
SNAPNAME
--------------------------------------------------------------------------------------------------------------------------------
SNAPSITE SNAPSHOT_ID FLAG REP_TYPE
-------------------------------------------------------------------------------------------------------------------------------- ----------- ---------- ----------
COMMENT$
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUERY_TXT
--------------------
KISH
MVREMOTE
EXHYDRA 21 2097253 2
SELECT "YTBL"."XNAME
" "XNAME","YTBL"."XI
D" "XID","YTBL"."CIT
Y" "CITY","YTBL"."CO
KISH
MVREMOTE
DB9ZX 81 2097257 2
After a complete refresh, the mview is back to FRESH mode
SQL> show user
USER is "SYS"
SQL> conn kish/password
Connected.
SQL> EXEC dbms_mview.refresh('MVREMOTE');
PL/SQL procedure successfully completed.
SQL> SELECT OWNER,MVIEW_NAME,STALENESS,STALE_SINCE from dba_mviews WHERE MVIEW_NAME='MVREMOTE';
OWNER MVIEW_NAME STALENESS STALE_SIN
-------------------- -------------------- ------------------- ---------
KISH MVREMOTE FRESH