合 Oracle数据泵导出导入物化视图ORA-39083和ORA-00942
Tags: Oracle逻辑导出导入物化视图ORA-00942ORA-39083
前言部分
导读和注意事项
各位技术爱好者,看完本文后,你可以掌握如下的技能,也可以学到一些其它你所不知道的知识,~O(∩_∩)O~:
① 如何使用数据泵导出和导入物化视图(重点)
② ORA-39083和ORA-00942错误解决
③ 数据泵的简单使用
④ parfile的使用
⑤ 数据泵生成dmp文件中的DDL语句
故障分析及解决过程
有网友问,物化视图是否能单独进行导出和导入呢?因为导出不报错,但是导入的时候报错了,报错信息如下所示:
网友给出的导出和导入的SQL语句如下所示:
1 2 | expdp system/oracle dumpfile=dumpdir:mview.dmp schemas=scott include=materialized_view impdp system/oracle dumpfile=dumpdir:mview.dmp |
导出和导入语句没毛病,小麦苗自己也测试了一下,的确如此,会报错的。
故障环境介绍
项目 | source db |
---|---|
db 类型 | |
db version | 11.2.0.3.0 |
db 存储 | |
OS版本及kernel版本 |
故障发生现象及报错信息
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 | 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 |
导入操作:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 | [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语句: