資料泵匯出匯入物化檢視（ORA-39083）

lhrbest發表於2018-05-31

資料泵匯出匯入物化檢視（ORA-39083）


1.1  BLOG文件結構圖

wps9E77.tmp 


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

微店地址


 

l 網名：小麥苗

l QQ：646634621

l QQ群:618766405

l 我的部落格：http://blog.itpub.net/26736162/abstract/1/

l 微信公眾號：xiaomaimiaolhr，二維碼如下：

wps9E88.tmp 

l 小麥苗的微信二維碼如下所示，加我時請備註相關資訊：

wps9E89.tmp 

l 我的微店地址：

l 出版的資料庫類叢書：http://blog.itpub.net/26736162/viewspace-2142121/

l 部落格連結：http://blog.itpub.net/26736162/abstract/1/

l 小麥苗課堂資料（影片+講課資料）：

 

 

1.3  故障分析及解決過程

有網友問，物化檢視是否能單獨進行匯出和匯入呢？因為匯出不報錯，但是匯入的時候報錯了，報錯資訊如下所示：

wps9E99.tmp 

網友給出的匯出和匯入的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

wps9ED9.tmp


單擊此項可新增到收藏夾 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:

 - IMPDP OF MATERIALIZED VIEW RESULTS IN ORA-39083 AND ORA-942 
 - 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. 



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寶典今日頭條號地址：

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

● 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

● 文章內容來源於小麥苗的學習筆記，部分整理自網路，若有侵權或不當之處還請諒解

● 版權所有，歡迎分享本文，轉載請保留出處

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

● 小麥苗的微店：

● 小麥苗出版的資料庫類叢書：http://blog.itpub.net/26736162/viewspace-2142121/

● 小麥苗OCP、OCM、高可用網路班：http://blog.itpub.net/26736162/viewspace-2148098/

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

使用微信客戶端掃描下面的二維碼來關注小麥苗的微信公眾號（xiaomaimiaolhr）及QQ群（DBA寶典），學習最實用的資料庫技術。

小麥苗的微信公眾號小麥苗的DBA寶典QQ群2《DBA筆試面寶典》讀者群小麥苗的微店

   小麥苗的微信公眾號      小麥苗的DBA寶典QQ群2     《DBA筆試面試寶典》讀者群       小麥苗的微店

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

資料泵匯出匯入物化檢視（ORA-39083）
 DBA筆試面試講解群
 《DBA寶典》讀者群 歡迎與我聯絡



來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26736162/viewspace-2155381/，如需轉載，請註明出處，否則將追究法律責任。

相關文章