Data Pumpも癖モノだよね〜w その6 - schemaモードでMviewを他のPDBへ複製（オプジェクトパスが不足すると...） Tweet

Previously on Mac De Oracle

前回は、schemaモードのData PumpでMviewを他のPDBへ複製する際、必要最小限のオブジェクト絞っても可能か? という確認でした。

なんだ、簡単じゃんと、思ったあなた！！
そうでもないんですよ！

SCHEMA_EXPORT_OBJECTSビューより、
オブジェクトパスの親子関係から親をエクスポートすれば子も一緒だねぇ〜。と想像はできるわけですが、
兄弟(TABLEやDB_LINKやMATERIALIZED_VIEW）の場合はそう簡単ではなくて、そもそも依存関係を把握してないとうまく使いこなせないんですよ！

というのが今日のネタ

FAQ!

---

前回同様、基本レプリケーション環境が完成した状態からエクスポートしてみます。

その前にパラメータファイルに含めるincludeパラメータを確認しておきましょう。
複製に成功したオブジェクトパスは以下でした。
SCHEMA_EXPORT_OBJECTSビューより）

OBJECT_PATH                     COMMENTS
------------------------------- ------------------------------------------------------------
SCHEMA_EXPORT/DB_LINK           Private database links in the selected schemas  
SCHEMA_EXPORT/TABLE             Tables in the selected schemas and their dependent objects
SCHEMA_EXPORT/MATERIALIZED_VIEW Materialized views
SCHEMA_EXPORT/JOB               Jobs in the selected schemas
SCHEMA_EXPORT/REFRESH_GROUP     Refresh groups in the selected schemas

上記の赤字のオブジェクト（DB_LINK/TABLE/MATERIALIZED_VIEW/JOB/REFRESH_GROUP)はSCHEMA_EXPORTを親とする兄弟という関係なのは見ての通り。

MVIEWをTABLEとして複製の回でお見せしたとおり、MVIEWであってもTABLEのパスでエクスポートすれば、それはMATERIALIZED_VIEWではなく、TABLEだけ（正確に言うと、TABLEとその子孫のオブジェクト）がエクスポートされていました。

つまり、兄弟の場合、親子の場合と異なり関連はあっても自動でエクスポートしてくれるわけではない。という癖が見えてきます。:)
はっ！　そうだったのか！　という方のために試してみます！

INCLUDE=TABLEとすると、Data Pumpも癖モノだよね〜w　その２ - Materialized ViewをTableとして移行するに書いたようにMviewではなくTABLEとしてエクスポートされ、インポートしてもTABLEのままでリフレッユジョブもリフレッシュグループもありませんでした。

では、INCLUDE=MATERIALIZED_VIEWとした場合には、リフレッシュジョブもリフレッシュグループもエクスポートされるんじゃないか？　（そうは、うまくはいかないんです！）

[oracle@vbgeneric ˜]$ cat exp_mviewonly.par 
schemas=mview_schema1
include=MATERIALIZED_VIEW:"IN ('MV_MASTER')"

では、その様子を再現w　よーく見ててね。

えーーーーーーっ!
うまくいった前回との違いおわかりですよね。　Mviewに含まれるデータもエクスポートされていませんし、索引、制約、リフレッシュジョブや、リフレッシュグループもエクスポートされていません！！！
なんということでしょうw　

オブジェクトパス：MATERIALIZED_VIEWに紐づくと思っていたオブジェクトは自動ではされてエクスポートされません！！！w　

[Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01":  system/********@orcl directory=workdir dumpfile=exp_mviewonly.dmp logfile=exp_mviewonly.log parfile=exp_mviewonly.par 
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/userhome/oracle/exp_mviewonly.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Tue Apr 25 23:40:55 2017 elapsed 0 00:00:15

この状態で、他のPDBにインポートとしてみます。ユーザ及び、権限とData Pump作業用ディレクリオブジェクトは事前に作成しておきます。

なんということでしょう！　Mviewが作成できません！
別な意味で、これにハマっている方もいるようです。
Problems creating MVIEW DDL using Data Pump and the SQLFILE option / Simon DBA Advanced Oracle Tips, Tricks, and How-To's

[oracle@vbgeneric ˜]$ sqlplus sys/oracle@orcl2 as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Tue Apr 25 23:52:49 2017

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

orcl2@SYS> create directory workdir as '/u01/userhome/oracle';

Directory created.

orcl2@SYS> create user mview_schema1 identified by welcome1
  2  default tablespace users
  3  temporary tablespace temp
  4  quota unlimited on users;

User created.

orcl2@SYS> grant create session, create table, create database link, create materialized view to mview_schema1;

Grant succeeded.

orcl2@SYS> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
[oracle@vbgeneric ˜]$ 
[oracle@vbgeneric ˜]$ impdp system/oracle@orcl2 directory=workdir dumpfile=exp_mviewonly.dmp logfile=imp_mviewonly.log 

Import: Release 12.1.0.2.0 - Production on Tue Apr 25 23:53:58 2017

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/********@orcl2 directory=workdir dumpfile=exp_mviewonly.dmp logfile=imp_mviewonly.log 
Processing object type SCHEMA_EXPORT/MATERIALIZED_VIEW
ORA-39083: Object type MATERIALIZED_VIEW:"MVIEW_SCHEMA1"."MV_MASTER" failed to create with error:
ORA-06550: line 1, column 9:
PLS-00352: Unable to access another database 'TO_MASTER_SCHEMA'
ORA-06550: line 1, column 9:
PLS-00201: identifier 'SYS@TO_MASTER_SCHEMA' must be declared
ORA-06550: line 1, column 9:
PL/SQL: Statement ignored
Failing sql is:
CREATE MATERIALIZED VIEW "MVIEW_SCHEMA1"."MV_MASTER" ("ID", "FOO") USING ("MV_MASTER", (10, 'ORCL', 1, 0, 0, "MASTER_SCHEMA", "MASTER", '2017-04-25 23:37:06', 
Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with 1 error(s) at Tue Apr 25 23:54:05 2017 elapsed 0 00:00:04

これ、どうやったら解決するんでしょう？
答えは、不足しているオプジェクトパスを指定する！。　なのですが、その前にいくつか確認。
(SCHEMA_EXPORT_OBJECTSビューのオブジェクトパスをよーく覗いて見るとよいと思います）

１つめの事実

include=TABLE だけでエクスポートするとMVIEWではなくTABLEとしてエクスポートされ、インポートしてもTABLEのまま。
だたし、CONSTARINTはincludeパラメータに含めなくても、TABLEに関連するCONSTRAINTとINDEXと統計はエクスポートされている。
かつ、データもエクスポートされる！！！　（オブジェクトパスの親子関係を見ると　TABLE_DATAは、TABLEの子、CONSTRAINT、INDEX、STATISTICSも子、実際の統計は孫）
DB_LINK、JOB、REFRESH_GROUPはエクスポートされない。（オブジェクトパスの階層を見るとそれらのオブジェクトはTABLEと同じ階層にある兄弟であることがわかります。）

２つめの事実

include=MATERIALIZED_VIEWだけでエクスポートするとMVIEWとしての定義はエクスポートされている。
オブジェクトパスを見ると、MATERIALIZED_VIEWには子孫は存在していません。親はSCHEMA_EXPORT。
なので、兄弟である、DB_LINK、TABLE、DB_LINK、JOB、REFRESH_GROUPエクスポートされない。かつ、どうやら、MATERIALIZED_VIEWに含まれるのはメタデータだけっぽい。（メタデータっぽいですが、それでだけMVIEWが作成できるわけでもない）

なかなかの癖モノですね。

なのであれば、
MATERIALIZED_VIEWのみエクスポートしてインポートした時に発生したMVIEW作成エラーを回避するオブジェクトパスは、DB_LINK, TABLE, MATERIALIZED_VIEWなんじゃないかなぁと思うわけです。はい。
ただし、完全ではありません。理由は、リフレッシュグループとリフレッシュジョブが含まれていなためです。前述のオブジェクトパスに加え、 JOB　と　REFRESH_GROUP を含めると、
前回のとおりMviewと関連オブジェクトのみエクスポート／インポートすることができるようになります。

では、DB_LINK, TABLE, MATERIALIZED_VIEWだけをエクスポートして、インポートしたら自動リフレッシュはできないけどMVIEWとして作成できるか確認してみます。

[oracle@vbgeneric ˜]$ cat exp_mviewonly.par 
schemas=mview_schema1
include=DB_LINK
include=TABLE:"IN ('MV_MASTER')"
include=MATERIALIZED_VIEW:"IN ('MV_MASTER')"

・・・中略・・・

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01":  system/********@orcl directory=workdir dumpfile=exp_mviewonly.dmp logfile=exp_mviewonly.log parfile=exp_mviewonly.par 
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type SCHEMA_EXPORT/DB_LINK
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/MATERIALIZED_VIEW
. . exported "MVIEW_SCHEMA1"."MV_MASTER"                   5.5 KB       2 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
  /u01/userhome/oracle/exp_mviewonly.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Wed Apr 26 00:20:46 2017 elapsed 0 00:00:34

うまくインポートできました。自動リフレッシュはできませんが。2行登録されているMVIEWが作成されました！！

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/********@orcl2 directory=workdir dumpfile=exp_mviewonly.dmp logfile=imp_mviewonly.log 
Processing object type SCHEMA_EXPORT/DB_LINK
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "MVIEW_SCHEMA1"."MV_MASTER"                   5.5 KB       2 rows
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/MATERIALIZED_VIEW
Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at Wed Apr 26 00:22:50 2017 elapsed 0 00:00:04

以下のとおり、CON_ID=5 (PDB:ORCL2)にはTABLE/INDEX/DB_LINK/MATERIALIZED_VIEWは作成されましたが、リフレッシュグループもリフレッシュジョブもありません。

orcl2@MVIEW_SCHEMA1> select object_name,object_type from user_objects;

OBJECT_NAME                    OBJECT_TYPE
------------------------------ -----------------------
MV_MASTER                      TABLE
SYS_C0014888                   INDEX
MV_MASTER                      MATERIALIZED VIEW
TO_MASTER_SCHEMA               DATABASE LINK

orcl2@MVIEW_SCHEMA1> select rowner,rname,refgroup,job,broken,interval,next_date from user_refresh;

no rows selected

orcl2@MVIEW_SCHEMA1> select job,log_user,schema_user,last_date,next_date,interval,failures,what from user_jobs;

no rows selected

ということで、おしまい。

---

Data Pumpも癖モノだよね〜w　その１ - queryパラメーターの解析タイミング
Data Pumpも癖モノだよね〜w　その２ - Materialized ViewをTableとして移行する
Data Pumpも癖モノだよね〜w　その3 - dbms_job と dbms_scheduler との複雑な関係
Data Pumpも癖モノだよね〜w　その4 - schemaモードでMviewを他のPDBへ複製
Data Pumpも癖モノだよね〜w　その4と1/2 - schemaモードでMviewを他のPDBへ複製 (紛らわしいステータスw）
Data Pumpも癖モノだよね〜w　その5 - schemaモードでMviewを他のPDBへ複製（オプジェクトパス de 絞り込み）