数据泵导出导入物化视图（ORA-39083）

2018-05-31 本文已影响9人小麦苗DB宝

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：001600

OCM认证每年N期，9课时/期每周二、周五20：00-22：0023000

高可用课程（rac+dg+ogg）每年1期，20课时左右/期每周一、周三、周四、周六20：00-22：002000

Oracle初级入门每年1期，15课时左右/期每周一、周三、周四、周六20：00-22：00800

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 version11.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

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> 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

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

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

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:

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

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

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

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";

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

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

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

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

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并不能创建和原物化视图一样的表，因此执行时会报错：

所以解决办法就很简单了，在导出的时候，需要导出和原物化视图同名的表即可。

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

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

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]$ impdp system/lhr dumpfile=mview4.dmp

Import: Release 11.2.0.3.0 - Production on Wed May 30 11:50:25 2018

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

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)

----------

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

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]$ impdp system/lhr dumpfile=mview5.dmp

Import: Release 11.2.0.3.0 - Production on Wed May 30 11:57:22 2018

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

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)

----------

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

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

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.

About Me

.............................................................................................................................................

● 本文作者：小麦苗，部分内容整理自网络，若有侵权请联系小麦苗删除

● 本文在itpub（http://blog.itpub.net/26736162/abstract/1/）、博客园（http://www.cnblogs.com/lhrbest）和个人微信公众号（xiaomaimiaolhr）上有同步更新

● 本文itpub地址：http://blog.itpub.net/26736162/abstract/1/

● 本文博客园地址：http://www.cnblogs.com/lhrbest

● 本文pdf版、个人简介及小麦苗云盘地址：http://blog.itpub.net/26736162/viewspace-1624453/

● 数据库笔试面试题库及解答：http://blog.itpub.net/26736162/viewspace-2134706/

● DBA宝典今日头条号地址：http://www.toutiao.com/c/user/6401772890/#mid=1564638659405826

.............................................................................................................................................

● QQ群号：230161599（满）、618766405

● 微信群：可加我微信，我拉大家进群，非诚勿扰

● 联系我请加QQ好友（646634621），注明添加缘由

● 于 2018-05-01 06:00 ~ 2018-05-31 24:00 在魔都完成

● 最新修改时间：2018-05-01 06:00 ~ 2018-05-31 24:00

● 文章内容来源于小麦苗的学习笔记，部分整理自网络，若有侵权或不当之处还请谅解

.............................................................................................................................................

● 小麦苗的微店：https://weidian.com/s/793741433?wfr=c&ifr=shopdetail

● 小麦苗出版的数据库类丛书：http://blog.itpub.net/26736162/viewspace-2142121/

● 小麦苗OCP、OCM、高可用网络班：http://blog.itpub.net/26736162/viewspace-2148098/

.............................................................................................................................................

使用微信客户端扫描下面的二维码来关注小麦苗的微信公众号（xiaomaimiaolhr）及QQ群（DBA宝典），学习最实用的数据库技术。

小麦苗的微信公众号小麦苗的DBA宝典QQ群2 《DBA笔试面试宝典》读者群小麦苗的微店

.............................................................................................................................................

数据泵导出导入物化视图（ORA-39083）

猜你喜欢

热点阅读