数据泵导出导入物化视图（ORA-39083）
1.1 BLOG文档结构图
1.2 前言部分
1.2.1 导读和注意事项
各位技术爱好者，看完本文后，你可以掌握如下的技能，也可以学到一些其它你所不知道的知识，~O(∩_∩)O~：
① 如何使用数据泵导出和导入物化视图（重点）
② ORA-39083和ORA-00942错误解决
③ 数据泵的简单使用
④ parfile的使用
⑤ 数据泵生成dmp文件中的DDL语句
Tips：
① 本文在itpub（http://blog.itpub.net/26736162）、博客园(http://www.cnblogs.com/lhrbest)和微信公众号（xiaomaimiaolhr）上有同步更新。
② 文章中用到的所有代码、相关软件、相关资料及本文的pdf版本都请前往小麦苗的云盘下载，小麦苗的云盘地址见：http://blog.itpub.net/26736162/viewspace-1624453/。
③ 若网页文章代码格式有错乱，请下载pdf格式的文档来阅读。
④ 本文适合于初中级人员阅读，数据库大师请略过本文。
⑤ 不喜勿喷。
本文若有错误或不完善的地方请大家多多指正，您的批评指正是我写作的最大动力。
1.2.2 小麦苗课程
小麦苗课堂开课啦，如下是现有的课程，欢迎咨询小麦苗：
课程名称 |
课时 |
上课时间（可根据情况调整） |
价格 |
|
OCP（从入门到专家） |
每年1期，35课时左右/期 |
每周一、周三、周四、周六 |
20：00-22：00 |
1600 |
OCM认证 |
每年N期，9课时/期 |
每周二、周五 |
20：00-22：00 |
23000 |
高可用课程（rac+dg+ogg） |
每年1期，20课时左右/期 |
每周一、周三、周四、周六 |
20：00-22：00 |
2000 |
Oracle初级入门 |
每年1期，15课时左右/期 |
每周一、周三、周四、周六 |
20：00-22：00 |
800 |
Oracle健康检查脚本 |
可微信或微店购买。 |
100 |
||
Oracle数据库技能直通车 |
包含如下3个课程： ①《11g OCP网络课程培训》（面向零基础） 价值1600元 ②《11g OCM网络班课程培训》（Oracle技能合集）价值10000+元 ③《RAC + DG + OGG 高可用网络班课程》 价值2000元 以上3个课程全部打包只要5888，只要5888所有课程带回家，终身指导！所有课程都是在线讲课，不是播放视频，课件全部赠送！ 注意：以上OCP和OCM课程只包括培训课程，不包括考试费用。 |
5888 |
||
注意：
1、每次上课前30分钟答疑。
2、OCM实时答疑，提供和考试一样的练习模拟环境，只要按照老师讲的方式来练习，可以保证100%通过。
3、授课方式：YY语音网络直播讲课（非视频） + QQ互动答疑 + 视频复习。其中，OCM在上海开设现场班。
4、OCP课时可以根据大家学习情况进行增加或缩减。
5、以上所有课程均可循环听课。
6、12c OCM课程私聊。
7、Oracle初级入门课程，只教大家最实用+最常用的Oracle操作维护知识。
培训项目 |
连接地址 |
DB笔试面试历史连接 |
http://mp.weixin.qq.com/s/Vm5PqNcDcITkOr9cQg6T7w |
OCP培训说明连接 |
https://mp.weixin.qq.com/s/2cymJ4xiBPtTaHu16HkiuA |
OCM培训说明连接 |
https://mp.weixin.qq.com/s/7-R6Cz8RcJKduVv6YlAxJA |
高可用（RAC+DG+OGG）培训说明连接 |
https://mp.weixin.qq.com/s/4vf042CnOdAD8zDyjUueiw |
OCP最新题库解析历史连接（052） |
http://mp.weixin.qq.com/s/bUgn4-uciSndji_pUbLZfA |
微店地址 |
https://weidian.com/s/793741433?wfr=c&ifr=shopdetail |
l 网名：小麦苗
l QQ：646634621
l QQ群:618766405
l 我的博客：http://blog.itpub.net/26736162/abstract/1/
l 微信公众号：xiaomaimiaolhr，二维码如下：
l 小麦苗的微信二维码如下所示，加我时请备注相关信息：
l 我的微店地址：https://weidian.com/s/793741433?wfr=c&ifr=shopdetail
l 出版的数据库类丛书：http://blog.itpub.net/26736162/viewspace-2142121/
l 博客链接：http://blog.itpub.net/26736162/abstract/1/
l 小麦苗课堂资料（视频+讲课资料）：https://share.weiyun.com/5fAdN5m
1.3 故障分析及解决过程
有网友问，物化视图是否能单独进行导出和导入呢？因为导出不报错，但是导入的时候报错了，报错信息如下所示：
网友给出的导出和导入的SQL语句如下所示：
|
expdp system/oracle dumpfile=dumpdir:mview.dmp schemas=scott include=materialized_view impdp system/oracle dumpfile=dumpdir:mview.dmp |
导出和导入语句没毛病，小麦苗自己也测试了一下，的确如此，会报错的。
1.3.1 故障环境介绍
项目 |
source db |
db 类型 |
|
db version |
11.2.0.3.0 |
db 存储 |
|
OS版本及kernel版本 |
|
1.3.2 故障发生现象及报错信息
|
ORA-39083: Object type MATERIALIZED_VIEW failed to create with error: ORA-00942: table or view does not exist Failing sql is: CREATE MATERIALIZED VIEW "LHR"."TEST_MV_LHR" ("OBJECT_ID", "OBJECT_NAME") USING ("TEST_MV_LHR", (10, 'OCPLHR1', 1, 0, 0, "LHR", "TEST_MV", '2018-05-30 10:58:06', 0, 76926, '1950-01-01 12:00:00', '', 0, 1372748, 0, NULL, (1, "OBJECT_ID", "OBJECT_ID", 0, 321, 0)), 2097249, 10, ('1950-01-01 12:00:00', 46, 0, 0, 1372748, 0, 0, 0, 1, NULL, NULL)) REFRESH FORCE WITH PRIMARY KEY AS SELECT " Job "LHR"."SYS_IMPORT_FULL_01" completed with 1 error(s) at 11:09:05 |
|
[oracle@OCPLHR ~]$ ORACLE_SID=OCPLHR1 [oracle@OCPLHR ~]$ ss
SQL*Plus: Release 11.2.0.3.0 Production on Wed May 30 10:55:41 2018
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options
SYS@OCPLHR1> conn lhr/lhr Connected. LHR@OCPLHR1> LHR@OCPLHR1> LHR@OCPLHR1> create table test_mv as select object_id,object_name from all_objects;
Table created.
LHR@OCPLHR1> alter table test_mv modify(object_id primary key);
Table altered.
LHR@OCPLHR1> select count(1) from test_mv;
COUNT(1) ---------- 72518
LHR@OCPLHR1> create materialized view log on test_mv ;
Materialized view log created.
LHR@OCPLHR1> create materialized view test_mv_lhr as select * from test_mv;
Materialized view created.
LHR@OCPLHR1> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options [oracle@OCPLHR ~]$ expdp system/lhr dumpfile=mview.dmp schemas=lhr include=materialized_view
Export: Release 11.2.0.3.0 - Production on Wed May 30 10:59:32 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options FLASHBACK automatically enabled to preserve database integrity. Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/******** dumpfile=mview.dmp schemas=lhr include=materialized_view Estimate in progress using BLOCKS method... Total estimation using BLOCKS method: 0 KB Processing object type SCHEMA_EXPORT/MATERIALIZED_VIEW Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is: /u01/app/oracle/admin/OCPLHR1/dpdump/mview.dmp Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at 11:00:01
|
导入操作：
|
[oracle@OCPLHR ~]$ ORACLE_SID=OCPLHR2 [oracle@OCPLHR ~]$ cp /u01/app/oracle/admin/OCPLHR1/dpdump/mview.dmp /u01/app/oracle/admin/OCPLHR2/dpdump/mview.dmp [oracle@OCPLHR ~]$
[oracle@OCPLHR ~]$ ss
SQL*Plus: Release 11.2.0.3.0 Production on Wed May 30 11:02:43 2018
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options
SYS@OCPLHR2> create user lhr identified by lhr;
User created.
SYS@OCPLHR2> grant dba to lhr;
Grant succeeded.
SYS@OCPLHR2> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options [oracle@OCPLHR ~]$ impdp system/lhr dumpfile=mview.dmp
Import: Release 11.2.0.3.0 - Production on Wed May 30 11:03:17 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/******** dumpfile=mview.dmp Processing object type SCHEMA_EXPORT/MATERIALIZED_VIEW ORA-39083: Object type MATERIALIZED_VIEW failed to create with error: ORA-00942: table or view does not exist Failing sql is: CREATE MATERIALIZED VIEW "LHR"."TEST_MV_LHR" ("OBJECT_ID", "OBJECT_NAME") USING ("TEST_MV_LHR", (10, 'OCPLHR1', 1, 0, 0, "LHR", "TEST_MV", '2018-05-30 10:58:06', 0, 76926, '1950-01-01 12:00:00', '', 0, 1372748, 0, NULL, (1, "OBJECT_ID", "OBJECT_ID", 0, 321, 0)), 2097249, 10, ('1950-01-01 12:00:00', 46, 0, 0, 1372748, 0, 0, 0, 1, NULL, NULL)) REFRESH FORCE WITH PRIMARY KEY AS SELECT " Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with 1 error(s) at 11:03:21
|
查看其DDL语句：
|
[oracle@OCPLHR ~]$ impdp system/lhr dumpfile=mview.dmp sqlfile=a.txt
Import: Release 11.2.0.3.0 - Production on Wed May 30 11:04:13 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Master table "SYSTEM"."SYS_SQL_FILE_FULL_01" successfully loaded/unloaded Starting "SYSTEM"."SYS_SQL_FILE_FULL_01": system/******** dumpfile=mview.dmp sqlfile=a.txt Processing object type SCHEMA_EXPORT/MATERIALIZED_VIEW Job "SYSTEM"."SYS_SQL_FILE_FULL_01" successfully completed at 11:04:16
[oracle@OCPLHR ~]$ cd /u01/app/oracle/admin/OCPLHR2/dpdump/ [oracle@OCPLHR dpdump]$ cat a.txt -- CONNECT SYSTEM ALTER SESSION SET EVENTS '10150 TRACE NAME CONTEXT FOREVER, LEVEL 1'; ALTER SESSION SET EVENTS '10904 TRACE NAME CONTEXT FOREVER, LEVEL 1'; ALTER SESSION SET EVENTS '25475 TRACE NAME CONTEXT FOREVER, LEVEL 1'; ALTER SESSION SET EVENTS '10407 TRACE NAME CONTEXT FOREVER, LEVEL 1'; ALTER SESSION SET EVENTS '10851 TRACE NAME CONTEXT FOREVER, LEVEL 1'; ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 192 '; -- new object type path: SCHEMA_EXPORT/MATERIALIZED_VIEW -- CONNECT LHR CREATE MATERIALIZED VIEW "LHR"."TEST_MV_LHR" ("OBJECT_ID", "OBJECT_NAME") USING ("TEST_MV_LHR", (10, 'OCPLHR1', 1, 0, 0, "LHR", "TEST_MV", '2018-05-30 10:58:06', 0, 76926, '1950-01-01 12:00:00', '', 0, 1372748, 0, NULL, (1, "OBJECT_ID", "OBJECT_ID", 0, 321, 0)), 2097249, 10, ('1950-01-01 12:00:00', 46, 0, 0, 1372748, 0, 0, 0, 1, NULL, NULL)) REFRESH FORCE WITH PRIMARY KEY AS SELECT "TEST_MV"."OBJECT_ID" "OBJECT_ID","TEST_MV"."OBJECT_NAME" "OBJECT_NAME" FROM "TEST_MV" "TEST_MV";
ALTER MATERIALIZED VIEW "LHR"."TEST_MV_LHR" COMPILE;
|
单独拿出来执行，也报错：
|
[oracle@OCPLHR dpdump]$ ss
SQL*Plus: Release 11.2.0.3.0 Production on Wed May 30 11:04:58 2018
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options
SYS@OCPLHR2> CREATE MATERIALIZED VIEW "LHR"."TEST_MV_LHR" ("OBJECT_ID", "OBJECT_NAME") USING ("TEST_MV_LHR", (10, 'OCPLHR1', 1, 0, 0, "LHR", "TEST_MV", '2018-05-30 10:58:06', 0, 76926, '1950-01-01 12:00:00', '', 0, 1372748, 0, NULL, (1, "OBJECT_ID", "OBJECT_ID", 0, 321, 0)), 2097249, 10, ('1950-01-01 12:00:00', 46, 0, 0, 1372748, 0, 0, 0, 1, NULL, NULL)) REFRESH FORCE WITH PRIMARY KEY AS SELECT "TEST_MV"."OBJECT_ID" "OBJECT_ID","TEST_MV"."OBJECT_NAME" "OBJECT_NAME" FROM "TEST_MV" "TEST_MV"; CREATE MATERIALIZED VIEW "LHR"."TEST_MV_LHR" ("OBJECT_ID", "OBJECT_NAME") USING ("TEST_MV_LHR", (10, 'OCPLHR1', 1, 0, 0, "LHR", "TEST_MV", '2018-05-30 10:58:06', 0, 76926, '1950-01-01 12:00:00', '', 0, 1372748, 0, NULL, (1, "OBJECT_ID", "OBJECT_ID", 0, 321, 0)), 2097249, 10, ('1950-01-01 12:00:00', 46, 0, 0, 1372748, 0, 0, 0, 1, NULL, NULL)) REFRESH FORCE WITH PRIMARY KEY AS SELECT "TEST_MV"."OBJECT_ID" "OBJECT_ID","TEST_MV"."OBJECT_NAME" "OBJECT_NAME" FROM "TEST_MV" "TEST_MV" * ERROR at line 1: ORA-00942: table or view does not exist
|
1.3.3 故障分析
非常奇怪。但是，基于SCHEMA模式导出和导入没有问题，那么可以尝试一下，然后查看其DDL语句，估计能找到一些蛛丝马迹：
|
[oracle@OCPLHR ~]$ expdp system/lhr dumpfile=mview3.dmp schemas=lhr
Export: Release 11.2.0.3.0 - Production on Wed May 30 11:38:34 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options FLASHBACK automatically enabled to preserve database integrity. Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/******** dumpfile=mview3.dmp schemas=lhr Estimate in progress using BLOCKS method... Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 15.06 MB Processing object type SCHEMA_EXPORT/USER Processing object type SCHEMA_EXPORT/SYSTEM_GRANT Processing object type SCHEMA_EXPORT/ROLE_GRANT Processing object type SCHEMA_EXPORT/DEFAULT_ROLE Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Processing object type SCHEMA_EXPORT/TABLE/TABLE Processing object type SCHEMA_EXPORT/TABLE/COMMENT Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX 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/TABLE/POST_TABLE_ACTION Processing object type SCHEMA_EXPORT/MATERIALIZED_VIEW Processing object type SCHEMA_EXPORT/TABLE/MATERIALIZED_VIEW_LOG . . exported "LHR"."TB_TMP" 7.270 MB 75216 rows . . exported "LHR"."TEST_MV" 2.307 MB 72518 rows . . exported "LHR"."TEST_MV_LHR" 2.307 MB 72518 rows . . exported "LHR"."TEST_UI" 26.71 KB 1 rows . . exported "LHR"."MLOG$_TEST_MV" 0 KB 0 rows Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is: /u01/app/oracle/admin/OCPLHR1/dpdump/mview3.dmp Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at 11:39:18
|
基于schema进行导入：
|
[oracle@OCPLHR dpdump]$ ss
SQL*Plus: Release 11.2.0.3.0 Production on Wed May 30 11:26:28 2018
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options
SYS@OCPLHR2> drop user lhr cascade;
User dropped.
SYS@OCPLHR2> create user lhr identified by lhr;
User created.
SYS@OCPLHR2> grant dba to lhr; Grant succeeded.
SYS@OCPLHR2> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options [oracle@OCPLHR ~]$ impdp system/lhr dumpfile=mview3.dmp
Import: Release 11.2.0.3.0 - Production on Wed May 30 11:41:42 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/******** dumpfile=mview3.dmp Processing object type SCHEMA_EXPORT/USER ORA-31684: Object type USER:"LHR" already exists Processing object type SCHEMA_EXPORT/SYSTEM_GRANT Processing object type SCHEMA_EXPORT/ROLE_GRANT Processing object type SCHEMA_EXPORT/DEFAULT_ROLE Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Processing object type SCHEMA_EXPORT/TABLE/TABLE Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA . . imported "LHR"."TEST_MV" 2.307 MB 72518 rows . . imported "LHR"."TEST_MV_LHR" 2.307 MB 72518 rows . . imported "LHR"."TEST_UI" 26.71 KB 1 rows . . imported "LHR"."MLOG$_TEST_MV" 0 KB 0 rows Processing object type SCHEMA_EXPORT/TABLE/COMMENT Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX 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/TABLE/POST_TABLE_ACTION Processing object type SCHEMA_EXPORT/MATERIALIZED_VIEW Processing object type SCHEMA_EXPORT/TABLE/MATERIALIZED_VIEW_LOG Job "SYSTEM"."SYS_SQL_FILE_FULL_01" successfully completed at 11:41:51
|
没有问题，那么查看一下它的DDL语句呢：
|
[oracle@OCPLHR ~]$ impdp system/lhr dumpfile=mview3.dmp sqlfile=c.txt
Import: Release 11.2.0.3.0 - Production on Wed May 30 11:42:48 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Master table "SYSTEM"."SYS_SQL_FILE_FULL_01" successfully loaded/unloaded Starting "SYSTEM"."SYS_SQL_FILE_FULL_01": system/******** dumpfile=mview3.dmp sqlfile=c.txt Processing object type SCHEMA_EXPORT/USER Processing object type SCHEMA_EXPORT/SYSTEM_GRANT Processing object type SCHEMA_EXPORT/ROLE_GRANT Processing object type SCHEMA_EXPORT/DEFAULT_ROLE Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Processing object type SCHEMA_EXPORT/TABLE/TABLE Processing object type SCHEMA_EXPORT/TABLE/COMMENT Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX 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/TABLE/POST_TABLE_ACTION Processing object type SCHEMA_EXPORT/MATERIALIZED_VIEW Processing object type SCHEMA_EXPORT/TABLE/MATERIALIZED_VIEW_LOG Job "SYSTEM"."SYS_SQL_FILE_FULL_01" successfully completed at 11:42:53
[oracle@OCPLHR ~]$ cd /u01/app/oracle/admin/OCPLHR2/dpdump [oracle@OCPLHR dpdump]$ cat c.txt -- CONNECT SYSTEM ALTER SESSION SET EVENTS '10150 TRACE NAME CONTEXT FOREVER, LEVEL 1'; ALTER SESSION SET EVENTS '10904 TRACE NAME CONTEXT FOREVER, LEVEL 1'; ALTER SESSION SET EVENTS '25475 TRACE NAME CONTEXT FOREVER, LEVEL 1'; ALTER SESSION SET EVENTS '10407 TRACE NAME CONTEXT FOREVER, LEVEL 1'; ALTER SESSION SET EVENTS '10851 TRACE NAME CONTEXT FOREVER, LEVEL 1'; ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 192 '; -- new object type path: SCHEMA_EXPORT/USER CREATE USER "LHR" IDENTIFIED BY VALUES 'S:B8183DC121F881C2FA1B308FC6F7ED3ED020707C59062FB3EC22F461E886;157AE4BCFD41976D' DEFAULT TABLESPACE "USERS" TEMPORARY TABLESPACE "TEMP"; -- new object type path: SCHEMA_EXPORT/SYSTEM_GRANT GRANT CREATE MATERIALIZED VIEW TO "LHR"; GRANT UNLIMITED TABLESPACE TO "LHR"; -- new object type path: SCHEMA_EXPORT/ROLE_GRANT GRANT "DBA" TO "LHR"; -- new object type path: SCHEMA_EXPORT/DEFAULT_ROLE ALTER USER "LHR" DEFAULT ROLE ALL; -- new object type path: SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA -- CONNECT LHR
BEGIN sys.dbms_logrep_imp.instantiate_schema(schema_name=>SYS_CONTEXT('USERENV','CURRENT_SCHEMA'), export_db_name=>'OCPLHR1', inst_scn=>'1380545'); COMMIT; END; / -- new object type path: SCHEMA_EXPORT/TABLE/TABLE -- CONNECT SYSTEM 。。。。。。。。。。。。。。 CREATE TABLE "LHR"."TEST_MV" ( "OBJECT_ID" NUMBER NOT NULL ENABLE, "OBJECT_NAME" VARCHAR2(30 BYTE) NOT NULL ENABLE ) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "USERS" ; 。。。。。。。。。。。。。。 CREATE TABLE "LHR"."TEST_MV_LHR" ( "OBJECT_ID" NUMBER NOT NULL ENABLE, "OBJECT_NAME" VARCHAR2(30 BYTE) NOT NULL ENABLE ) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "USERS" ; 。。。。。。。。。。 -- new object type path: SCHEMA_EXPORT/TABLE/COMMENT COMMENT ON TABLE "LHR"."MLOG$_TEST_MV" IS 'snapshot log for master table LHR.TEST_MV'; COMMENT ON TABLE "LHR"."RUPD$_TEST_MV" IS 'temporary updatable snapshot log'; COMMENT ON MATERIALIZED VIEW "LHR"."TEST_MV_LHR" IS 'snapshot table for snapshot LHR.TEST_MV_LHR'; -- new object type path: SCHEMA_EXPORT/TABLE/INDEX/INDEX -- CONNECT LHR CREATE INDEX "LHR"."I_TU" ON "LHR"."TEST_UI" ("TABLE_NAME") PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING STORAGE( INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "EXAMPLE" PARALLEL 1 ;
ALTER INDEX "LHR"."I_TU" NOPARALLEL;
ALTER INDEX "LHR"."I_TU" UNUSABLE; -- new object type path: SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT -- CONNECT SYSTEM ALTER TABLE "LHR"."TEST_MV" ADD PRIMARY KEY ("OBJECT_ID") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "USERS" ENABLE; ALTER TABLE "LHR"."TEST_MV_LHR" ADD PRIMARY KEY ("OBJECT_ID") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "USERS" ENABLE; -- new object type path: SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS -- new object type path: SCHEMA_EXPORT/MATERIALIZED_VIEW CREATE MATERIALIZED VIEW "LHR"."TEST_MV_LHR" ("OBJECT_ID", "OBJECT_NAME") USING ("TEST_MV_LHR", (10, 'OCPLHR1', 1, 0, 0, "LHR", "TEST_MV", '2018-05-30 11:25:11', 0, 76926, '1950-01-01 12:00:00', '', 0, 1378477, 0, NULL, (1, "OBJECT_ID", "OBJECT_ID", 0, 321, 0)), 2097249, 10, ('1950-01-01 12:00:00', 62, 0, 0, 1378477, 0, 0, 0, 1, NULL, NULL)) REFRESH FORCE WITH PRIMARY KEY AS SELECT "TEST_MV"."OBJECT_ID" "OBJECT_ID","TEST_MV"."OBJECT_NAME" "OBJECT_NAME" FROM "TEST_MV" "TEST_MV";
ALTER MATERIALIZED VIEW "LHR"."TEST_MV_LHR" COMPILE; -- new object type path: SCHEMA_EXPORT/TABLE/MATERIALIZED_VIEW_LOG -- CONNECT SYSTEM CREATE MATERIALIZED VIEW LOG ON "LHR"."TEST_MV" WITH PRIMARY KEY USING ("MLOG$_TEST_MV", (10, 'OCPLHR1', 270434, '2018-05-30 11:25:11', '2018-05-30 11:25:11', '2018-05-30 10:56:34', '4000-01-01 00:00:00', '4000-01-01 00:00:00', '4000-01-01 00:00:00', 1, "OBJECT_ID", '2018-05-30 10:56:34', 2, 1, 62, '2018-05-30 11:25:11', 1378477, ("RUPD$_TEST_MV"))); [oracle@OCPLHR dpdump]$ ss
|
果然找到一点蛛丝马迹，在创建物化视图之前，竟然创建了一张和物化视图同名的表。于是乎，测试一下：
|
create materialized VIEW mv_lhr as select * from lhr.tb_tmp; SELECT * FROM dba_tables d WHERE d.TABLE_NAME LIKE '%MV_LHR%' ; SELECT * FROM Dba_Mviews; |
果然如此。
由此可知，在使用数据泵导出和导入物化视图时，以下SQL并不能创建和原物化视图一样的表，因此执行时会报错：
CREATE MATERIALIZED VIEW "LHR"."TEST_MV_LHR" ("OBJECT_ID", "OBJECT_NAME") USING ("TEST_MV_LHR", (10, 'OCPLHR1', 1, 0, 0, "LHR", "TEST_MV", '2018-05-30 10:58:06', 0, 76926, '1950-01-01 12:00:00', '', 0, 1372748, 0, NULL, (1, "OBJECT_ID", "OBJECT_ID", 0, 321, 0)), 2097249, 10, ('1950-01-01 12:00:00', 46, 0, 0, 1372748, 0, 0, 0, 1, NULL, NULL)) REFRESH FORCE WITH PRIMARY KEY AS SELECT "TEST_MV"."OBJECT_ID" "OBJECT_ID","TEST_MV"."OBJECT_NAME" "OBJECT_NAME" FROM "TEST_MV" "TEST_MV"; |
所以解决办法就很简单了，在导出的时候，需要导出和原物化视图同名的表即可。
1.3.4 故障解决
|
expdp system/lhr dumpfile=mview4.dmp schemas=lhr include=TABLE:\"IN \(\'TEST_MV_LHR\'\)\",materialized_view:\"IN \(\'TEST_MV_LHR\'\)\" impdp system/lhr dumpfile=mview4.dmp |
导出：
|
[oracle@OCPLHR ~]$ expdp system/lhr dumpfile=mview4.dmp schemas=lhr include=TABLE:\"IN \(\'TEST_MV_LHR\'\)\",materialized_view:\"IN \(\'TEST_MV_LHR\'\)\"
Export: Release 11.2.0.3.0 - Production on Wed May 30 11:48:16 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options FLASHBACK automatically enabled to preserve database integrity. Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/******** dumpfile=mview4.dmp schemas=lhr include=TABLE:"IN ('TEST_MV_LHR')",materialized_view:"IN ('TEST_MV_LHR')" Estimate in progress using BLOCKS method... Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 0 KB Processing object type SCHEMA_EXPORT/TABLE/TABLE Processing object type SCHEMA_EXPORT/MATERIALIZED_VIEW . . exported "LHR"."TEST_MV_LHR" 0 KB 0 rows Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is: /u01/app/oracle/admin/OCPLHR1/dpdump/mview4.dmp Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at 11:48:46
|
导入：
|
[oracle@OCPLHR dpdump]$ cp /u01/app/oracle/admin/OCPLHR1/dpdump/mview4.dmp /u01/app/oracle/admin/OCPLHR2/dpdump/mview4.dmp [oracle@OCPLHR dpdump]$ ss
SQL*Plus: Release 11.2.0.3.0 Production on Wed May 30 11:49:09 2018
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options
SYS@OCPLHR2> drop user lhr cascade;
User dropped.
SYS@OCPLHR2> create user lhr identified by lhr;
User created.
SYS@OCPLHR2> grant dba to lhr;
Grant succeeded.
SYS@OCPLHR2> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options [oracle@OCPLHR dpdump]$ [oracle@OCPLHR dpdump]$ [oracle@OCPLHR dpdump]$ impdp system/lhr dumpfile=mview4.dmp
Import: Release 11.2.0.3.0 - Production on Wed May 30 11:50:25 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Master table "SYSTEM"."SYS_IMPORT_FULL_02" successfully loaded/unloaded Starting "SYSTEM"."SYS_IMPORT_FULL_02": system/******** dumpfile=mview4.dmp Processing object type SCHEMA_EXPORT/TABLE/TABLE Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA . . imported "LHR"."TEST_MV_LHR" 0 KB 0 rows Processing object type SCHEMA_EXPORT/MATERIALIZED_VIEW Job "SYSTEM"."SYS_IMPORT_FULL_02" successfully completed at 11:50:29
[oracle@OCPLHR dpdump]$ ss
SQL*Plus: Release 11.2.0.3.0 Production on Wed May 30 11:50:36 2018
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options
SYS@OCPLHR2> select count(1) from "LHR"."TEST_MV_LHR";
COUNT(1) ---------- 0
SYS@OCPLHR2> exit
|
可以看到，导入不报错了，但是，物化视图并没有数据。所以，还需要将物化视图的基本加上，这样才能将数据导出：
|
expdp system/lhr dumpfile=mview5.dmp schemas=lhr include=TABLE:\"IN \(\'TEST_MV_LHR\',\'TEST_MV\'\)\",materialized_view:\"IN \(\'TEST_MV_LHR\'\)\" impdp system/lhr dumpfile=mview4.dmp exec dbms_mview.refresh('TEST_MV_LHR','C'); |
导出：
|
[oracle@OCPLHR ~]$ expdp system/lhr dumpfile=mview5.dmp schemas=lhr include=TABLE:\"IN \(\'TEST_MV_LHR\',\'TEST_MV\'\)\",materialized_view:\"IN \(\'TEST_MV_LHR\'\)\"
Export: Release 11.2.0.3.0 - Production on Wed May 30 11:56:08 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options FLASHBACK automatically enabled to preserve database integrity. Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/******** dumpfile=mview5.dmp schemas=lhr include=TABLE:"IN ('TEST_MV_LHR','TEST_MV')",materialized_view:"IN ('TEST_MV_LHR')" Estimate in progress using BLOCKS method... Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 3 MB Processing object type SCHEMA_EXPORT/TABLE/TABLE Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type SCHEMA_EXPORT/TABLE/POST_TABLE_ACTION Processing object type SCHEMA_EXPORT/MATERIALIZED_VIEW . . exported "LHR"."TEST_MV" 2.307 MB 72518 rows . . exported "LHR"."TEST_MV_LHR" 0 KB 0 rows Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is: /u01/app/oracle/admin/OCPLHR1/dpdump/mview5.dmp Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at 11:56:39
|
导入：
|
SYS@OCPLHR2> drop user lhr cascade;
User dropped.
SYS@OCPLHR2> create user lhr identified by lhr;
User created.
SYS@OCPLHR2> grant dba to lhr;
Grant succeeded.
SYS@OCPLHR2> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options [oracle@OCPLHR dpdump]$ [oracle@OCPLHR dpdump]$ cp /u01/app/oracle/admin/OCPLHR1/dpdump/mview5.dmp /u01/app/oracle/admin/OCPLHR2/dpdump/mview5.dmp [oracle@OCPLHR dpdump]$ [oracle@OCPLHR dpdump]$ [oracle@OCPLHR dpdump]$ [oracle@OCPLHR dpdump]$ impdp system/lhr dumpfile=mview5.dmp
Import: Release 11.2.0.3.0 - Production on Wed May 30 11:57:22 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/******** dumpfile=mview5.dmp Processing object type SCHEMA_EXPORT/TABLE/TABLE Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA . . imported "LHR"."TEST_MV" 2.307 MB 72518 rows . . imported "LHR"."TEST_MV_LHR" 0 KB 0 rows Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type SCHEMA_EXPORT/TABLE/POST_TABLE_ACTION Processing object type SCHEMA_EXPORT/MATERIALIZED_VIEW Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at 11:57:28
[oracle@OCPLHR dpdump]$ ss
SQL*Plus: Release 11.2.0.3.0 Production on Wed May 30 11:57:43 2018
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options
SYS@OCPLHR2> conn lhr/lhr Connected. LHR@OCPLHR2> select count(1) from "LHR"."TEST_MV_LHR" ;
COUNT(1) ---------- 0
LHR@OCPLHR2> exec dbms_mview.refresh('TEST_MV_LHR','C');
PL/SQL procedure successfully completed.
LHR@OCPLHR2> select count(1) from "LHR"."TEST_MV_LHR" ;
COUNT(1) ---------- 72518
|
完美导出。
最后查询MOS，在MOS中搜到一篇文章，如下：
Impdp of Materialized View Results in ORA-39083 and ORA-942 (文档 ID 549843.1)
其解决方案和我的一致。
最后，再说一个内容，如果命令中的转义字符看不懂（其实：单引号、双引号、小括号 都需要进行转义），或不会写，那么可以使用parfile参数来修改：
|
[oracle@OCPLHR ~]$ cat par_lhr.par include=TABLE:"IN ('TEST_MV_LHR','TEST_MV')",materialized_view:"IN ('TEST_MV_LHR')" [oracle@OCPLHR ~]$ expdp system/lhr dumpfile=mview6.dmp schemas=lhr parfile=par_lhr.par
Export: Release 11.2.0.3.0 - Production on Wed May 30 21:22:25 2018
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options FLASHBACK automatically enabled to preserve database integrity. Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/******** dumpfile=mview6.dmp schemas=lhr parfile=par_lhr.par Estimate in progress using BLOCKS method... Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 3 MB Processing object type SCHEMA_EXPORT/TABLE/TABLE Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type SCHEMA_EXPORT/TABLE/POST_TABLE_ACTION . . exported "LHR"."TEST_MV" 2.307 MB 72518 rows ORA-39168: Object path MATERIALIZED_VIEW was not found. Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is: /u01/app/oracle/admin/OCPLHR1/dpdump/mview6.dmp Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" completed with 1 error(s) at 21:23:07
[oracle@OCPLHR ~]$
|
1.4 故障处理总结
1、使用数据泵进行导出和导入时，基于schema和数据库级别可以导出和导入物化视图。
2、使用数据泵单独导出和导入物化视图（include=materialized_view）时，会报ORA-39083和ORA-00942错误。
3、在新建一个物化视图时，会同步新建一个同名的表。所以，使用数据泵单独导出和导入物化视图（include=materialized_view）时，需要加上这些同名的表。
4、INCLUDE进行导出和导入时只会导出和导入显式指定的数据库对象，而其依赖的对象并不会进行导出和导入。
5、若只导出物化视图的创建语句，则可以使用如下SQL：
|
expdp system/lhr dumpfile=mview4.dmp schemas=lhr include=TABLE:\"IN \(\'TEST_MV_LHR\'\)\",materialized_view:\"IN \(\'TEST_MV_LHR\'\)\" impdp system/lhr dumpfile=mview4.dmp |
若需要同步导出物化视图的创建语句及其存储的数据，则可以使用如下SQL：
|
expdp system/lhr dumpfile=mview5.dmp schemas=lhr include=TABLE:\"IN \(\'TEST_MV_LHR\',\'TEST_MV\'\)\",materialized_view:\"IN \(\'TEST_MV_LHR\'\)\" impdp system/lhr dumpfile=mview4.dmp exec dbms_mview.refresh('TEST_MV_LHR','C'); |
6、同步数据可以使用如下的SQL语句：
exec dbms_mview.refresh('TEST_MV_LHR','C'); |
1.5 用到的SQL集合
|
create table test_mv as select object_id,object_name from all_objects; alter table test_mv modify(object_id primary key); create materialized view log on test_mv ; create materialized view test_mv_lhr as select * from test_mv;
|
TEST_MV是基表 TEST_MV_LHR是物化视图
--不同步数据
|
expdp system/lhr dumpfile=mview4.dmp schemas=lhr include=TABLE:\"IN \(\'TEST_MV_LHR\'\)\",materialized_view:\"IN \(\'TEST_MV_LHR\'\)\" impdp system/lhr dumpfile=mview4.dmp
|
--TEST_MV是基表 同步数据
|
expdp system/lhr dumpfile=mview5.dmp schemas=lhr include=TABLE:\"IN \(\'TEST_MV_LHR\',\'TEST_MV\'\)\",materialized_view:\"IN \(\'TEST_MV_LHR\'\)\" impdp system/lhr dumpfile=mview4.dmp exec dbms_mview.refresh('TEST_MV_LHR','C');
|
parfile的使用：
|
[oracle@OCPLHR ~]$ cat par_lhr.par include=TABLE:"IN ('TEST_MV_LHR','TEST_MV')",materialized_view:"IN ('TEST_MV_LHR')" [oracle@OCPLHR ~]$ expdp system/lhr dumpfile=mview6.dmp schemas=lhr parfile=par_lhr.par
|
1.6 参考文章
1.6.1 MOS
|
Impdp of Materialized View Results in ORA-39083 and ORA-942 (文档 ID 549843.1) 转到底部 In this Document Symptoms Cause Solution References APPLIES TO: Oracle Database - Enterprise Edition - Version 10.2.0.1 and later Information in this document applies to any platform. ***Checked for relevance on 24-Jun-2013***SYMPTOMS Expdp with: INCLUDE=MATERIALIZED_VIEW:"IN ('')" does not include the snapshot table the Materialized View is based on. So during impdp, next errors are reported: ORA-39083: Object type MATERIALIZED_VIEW failed to create with error: ORA-942: table or view does not exist CAUSE This issue is described in the following bugs:Bug 6010532 - IMPDP OF MATERIALIZED VIEW RESULTS IN ORA-39083 AND ORA-942 Bug 6271249 - IMPDP OF MATERIALIED VIEWS RESULTS IN ERROR ORA-39083 ORA-942 These bugs are closed wit status 'Not a Bug'. SOLUTION The problem is that materialized view container tables are not exported when we do the export with INCLUDE=MATERIALIZED_VIEW:"IN ('')". When INCLUDE parameter is used, only object types explicitly specified in INCLUDE statements and their dependent objects are exported. Since only materialized view gets exported, the import fails with ORA-39083/ORA-942 error when creating the materialized view. This is an expected behavior because there are no dependent objects for materialized views. The workaround is to specify materialized view container tables as well as materialized view for INCLUDE parameter value: INCLUDE=TABLE:"IN ('MY_MV')", MATERIALIZED_VIEW:"IN ('MY_MV')" This behavior is documented in Oracle Database Utilities 10gR2, Chapter 2, section about parameter INCLUDE. http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/dp_export.htm#i1007837 Unpublished Enhancement Request 6743394 was logged for this issue. |