マテリアライズド・ビューとData Pumpインポートを試す (Oracle Database 19c)

マテリアライズド・ビューをData Pumpで移行する際の動作を検証します。

# マテリアライズド・ビューの作成

リモート・テーブルを参照するためにデータベース・リンクとマテリアライズド・ビューを作成します。

```
SQL> CREATE DATABASE LINK link1 CONNECT TO scott IDENTIFIED BY password USING 'O19A';

データベース・リンクが作成されました。

SQL> CREATE MATERIALIZED VIEW mview1 REFRESH FAST AS SELECT * FROM mtest1@link1;

マテリアライズド・ビューが作成されました。

```

# マテリアライズド・ビューのエクスポート

　Data Pumpのエクスポートを行います。マテリアライズド・ビューなので、データは本来不要ですが、この例では動きをわかりやすくするためにデータもエクスポートします。接続ユーザーはSYSTEM、マテリアライズド・ビューの所有ユーザーは MVUSR1 として、SCHEMAS パラメーターで指定します。


```
$ expdp userid=SYSTEM/password SCHEMAS=mvusr1 DUMPFILE=mvusr1.dmp

Export: Release 19.0.0.0.0 - Production on 月 12月 2 00:47:33 2019
Version 19.3.0.0.0

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

接続先: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
"SYSTEM"."SYS_EXPORT_SCHEMA_01"を起動しています: userid=SYSTEM/******** SCHEMAS=mvusr1 DUMPFILE=mvusr1.dmp
オブジェクト型SCHEMA_EXPORT/TABLE/TABLE_DATAの処理中です
オブジェクト型SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICSの処理中です
オブジェクト型SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICSの処理中です
オブジェクト型SCHEMA_EXPORT/STATISTICS/MARKERの処理中です
オブジェクト型SCHEMA_EXPORT/USERの処理中です
オブジェクト型SCHEMA_EXPORT/SYSTEM_GRANTの処理中です
オブジェクト型SCHEMA_EXPORT/DEFAULT_ROLEの処理中です
オブジェクト型SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMAの処理中です
オブジェクト型SCHEMA_EXPORT/DB_LINKの処理中です
オブジェクト型SCHEMA_EXPORT/TABLE/TABLEの処理中です
オブジェクト型SCHEMA_EXPORT/TABLE/COMMENTの処理中です
オブジェクト型SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINTの処理中です
オブジェクト型SCHEMA_EXPORT/MATERIALIZED_VIEWの処理中です
. . "MVUSR1"."MVIEW1"                           142.0 KB   10003行がエクスポートされました
マスター表"SYSTEM"."SYS_EXPORT_SCHEMA_01"は正常にロード/アンロードされました
******************************************************************************
SYSTEM.SYS_EXPORT_SCHEMA_01に設定されたダンプ・ファイルは次のとおりです:
  /u01/app/oracle/admin/O19A/dpdump/mvusr1.dmp
ジョブ"SYSTEM"."SYS_EXPORT_SCHEMA_01"が月 12月 2 00:49:46 2019 elapsed 0 00:02:09で正常に完了しました

```

マテリアライズド・ビューは SCHEMA_EXPORT/MATERIALIZED_VIEW のパスでエクスポートされていることがわかります。

# マテリアライズド・ビューのインポート
　条件を変えてマテリアライズド・ビューをインポートします。


## テーブル以外のインポート

まず EXCLUDE 句を使い、テーブルを除いてインポートを行います。

```
$ impdp userid=SYSTEM/password SCHEMAS=mvusr1 DUMPFILE=mvusr1.dmp EXCLUDE=TABLE

Import: Release 19.0.0.0.0 - Production on 月 12月 2 00:51:39 2019
Version 19.3.0.0.0

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

接続先: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
マスター表"SYSTEM"."SYS_IMPORT_SCHEMA_01"は正常にロード/アンロードされました
"SYSTEM"."SYS_IMPORT_SCHEMA_01"を起動しています: userid=SYSTEM/******** SCHEMAS=mvusr1 DUMPFILE=mvusr1.dmp EXCLUDE=TABLE
オブジェクト型SCHEMA_EXPORT/USERの処理中です
オブジェクト型SCHEMA_EXPORT/SYSTEM_GRANTの処理中です
オブジェクト型SCHEMA_EXPORT/DEFAULT_ROLEの処理中です
オブジェクト型SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMAの処理中です
オブジェクト型SCHEMA_EXPORT/DB_LINKの処理中です
オブジェクト型SCHEMA_EXPORT/MATERIALIZED_VIEWの処理中です
ORA-39083: オブジェクト型MATERIALIZED_VIEW:"MVUSR1"."MVIEW1"の作成が次のエラーで失敗しました:
ORA-00942: 表またはビューが存在しません。

エラー文は次のとおりです:
CREATE MATERIALIZED VIEW "MVUSR1"."MVIEW1" ("C1", "C2") USING ("MVIEW1", (10, 'O19A', 1, 0, 0, "SCOTT", "MTEST1", '2019-12-02 00:45:49', 0, 76315, '1950-01-01 12:00:00', '', 0, 9022650, 0, NULL, (1, "C1", "C1", 0, 321, 0)), 2097249, 10, ('1950-01-01 12:00:00', 186, 0, 0, 9022650, 0, 0, 4194304, 1, NULL, NULL), '@"LINK1"') REFRESH FAST WITH PRIMARY KEY AS SELECT "MTEST1"."C1" "C1","MTEST1"."C2" "C2" FROM "MTEST1"@"LINK1" "MTEST1"

ジョブ"SYSTEM"."SYS_IMPORT_SCHEMA_01"が完了しましたが、1エラーが月 12月 2 00:51:51 2019 elapsed 0 00:00:09で発生しています

``` 

「ORA-942: 表またはビューが存在しません。」というエラーでインポート処理が失敗します。


## マテリアライズド・ビュー以外をインポート

インポートしたユーザーを削除した後、再度 EXCLUDE 句を使い、マテリアライズド・ビューを除いてインポートを行います。

```
$ impdp userid=SYSTEM/password SCHEMAS=mvusr1 DUMPFILE=mvusr1.dmp EXCLUDE=MATERIALIZED_VIEW

Import: Release 19.0.0.0.0 - Production on 月 12月 2 00:53:53 2019
Version 19.3.0.0.0

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

接続先: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
マスター表"SYSTEM"."SYS_IMPORT_SCHEMA_01"は正常にロード/アンロードされました
"SYSTEM"."SYS_IMPORT_SCHEMA_01"を起動しています: userid=SYSTEM/******** SCHEMAS=mvusr1 DUMPFILE=mvusr1.dmp EXCLUDE=MATERIALIZED_VIEW
オブジェクト型SCHEMA_EXPORT/USERの処理中です
オブジェクト型SCHEMA_EXPORT/SYSTEM_GRANTの処理中です
オブジェクト型SCHEMA_EXPORT/DEFAULT_ROLEの処理中です
オブジェクト型SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMAの処理中です
オブジェクト型SCHEMA_EXPORT/DB_LINKの処理中です
オブジェクト型SCHEMA_EXPORT/TABLE/TABLEの処理中です
オブジェクト型SCHEMA_EXPORT/TABLE/TABLE_DATAの処理中です
. . "MVUSR1"."MVIEW1"                           142.0 KB   10003行がインポートされました
オブジェクト型SCHEMA_EXPORT/TABLE/COMMENTの処理中です
オブジェクト型SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINTの処理中です
オブジェクト型SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICSの処理中です
オブジェクト型SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICSの処理中です
オブジェクト型SCHEMA_EXPORT/STATISTICS/MARKERの処理中です
ジョブ"SYSTEM"."SYS_IMPORT_SCHEMA_01"が月 12月 2 00:54:34 2019 elapsed 0 00:00:39で正常に完了しました

```

SCHEMA_EXPORT/TABLE/TABLE_DATA のパスで「テーブル」としてインポートされています。この状態ではマテリアライズド・ビューと同じ名前のテーブルが作成されています。

## 動作するSQL文の確認

パラメーター SQLFILE を指定して、インポート時に実行される SQL 文を出力します。

```
$ impdp userid=SYSTEM/password SCHEMAS=mvusr1 DUMPFILE=mvusr1.dmp SQLFILE=mvusr1.sql

Import: Release 19.0.0.0.0 - Production on 月 12月 2 00:55:35 2019
Version 19.3.0.0.0

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

接続先: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
マスター表"SYSTEM"."SYS_SQL_FILE_SCHEMA_01"は正常にロード/アンロードされました
"SYSTEM"."SYS_SQL_FILE_SCHEMA_01"を起動しています: userid=SYSTEM/******** SCHEMAS=mvusr1 DUMPFILE=mvusr1.dmp SQLFILE=mvusr1.sql
オブジェクト型SCHEMA_EXPORT/USERの処理中です
オブジェクト型SCHEMA_EXPORT/SYSTEM_GRANTの処理中です
オブジェクト型SCHEMA_EXPORT/DEFAULT_ROLEの処理中です
オブジェクト型SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMAの処理中です
オブジェクト型SCHEMA_EXPORT/DB_LINKの処理中です
オブジェクト型SCHEMA_EXPORT/TABLE/TABLEの処理中です
オブジェクト型SCHEMA_EXPORT/TABLE/COMMENTの処理中です
オブジェクト型SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINTの処理中です
オブジェクト型SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICSの処理中です
オブジェクト型SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICSの処理中です
オブジェクト型SCHEMA_EXPORT/STATISTICS/MARKERの処理中です
オブジェクト型SCHEMA_EXPORT/MATERIALIZED_VIEWの処理中です
ジョブ"SYSTEM"."SYS_SQL_FILE_SCHEMA_01"が月 12月 2 00:55:41 2019 elapsed 0 00:00:05で正常に完了しました
```

まず CREATE TABLE 文でテーブルを作成しています。

```
CREATE TABLE "MVUSR1"."MVIEW1"
  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" ;
```

COMMENT 文コメントを出力し、ALTER TABLE 文で主キー制約等を指定します。

```
-- new object type path: SCHEMA_EXPORT/TABLE/COMMENT
 COMMENT ON MATERIALIZED VIEW "MVUSR1"."MVIEW1"  IS 'snapshot table for snapshot MVUSR1.MVIEW1';
-- new object type path: SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
ALTER TABLE "MVUSR1"."MVIEW1" ADD PRIMARY KEY ("C1")
  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;
```

最後に CREATE MATERIALIZED VIEW 文でマニュアルに無い USING 句を指定してテーブルをマテリアライズド・ビューとして利用しています。。

```
CREATE MATERIALIZED VIEW "MVUSR1"."MVIEW1" ("C1", "C2") 
USING ("MVIEW1", (10, 'O19A', 1, 0, 0, "SCOTT", "MTEST1", '2019-12-02 00:45:49', 0, 76315, '1950-01-01 12:00:00', '', 0, 9022650, 0, NULL, (1, "C1", "C1", 0, 321, 0)), 2097249, 10, ('1950-01-01 12:00:00', 186, 0, 0, 9022650, 0, 0, 4194304, 1, NULL, NULL), '@"LINK1"') 
REFRESH FAST WITH PRIMARY KEY AS 
SELECT "MTEST1"."C1" "C1","MTEST1"."C2" "C2" FROM "MTEST1"@"LINK1" "MTEST1";
```

最後に ALTER MATERIALIZED VIEW 文でマテリアライズド・ビューのコンパイルを行っています。

```
 ALTER MATERIALIZED VIEW "MVUSR1"."MVIEW1" COMPILE;
```

## リモート・インスタンス停止状態
　マテリアライズド・ビューが参照するリモート・インスタンスを停止した状態でインポートを行いました。

```
$ impdp userid=SYSTEM/password SCHEMAS=mvusr1 DUMPFILE=mvusr1.dmp

Import: Release 19.0.0.0.0 - Production on 月 12月 2 01:25:41 2019
Version 19.3.0.0.0

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

接続先: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
マスター表"SYSTEM"."SYS_IMPORT_SCHEMA_01"は正常にロード/アンロードされました
"SYSTEM"."SYS_IMPORT_SCHEMA_01"を起動しています: userid=SYSTEM/******** SCHEMAS=mvusr1 DUMPFILE=mvusr1.dmp
オブジェクト型SCHEMA_EXPORT/USERの処理中です
オブジェクト型SCHEMA_EXPORT/SYSTEM_GRANTの処理中です
オブジェクト型SCHEMA_EXPORT/DEFAULT_ROLEの処理中です
オブジェクト型SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMAの処理中です
オブジェクト型SCHEMA_EXPORT/DB_LINKの処理中です
オブジェクト型SCHEMA_EXPORT/TABLE/TABLEの処理中です
オブジェクト型SCHEMA_EXPORT/TABLE/TABLE_DATAの処理中です
. . "MVUSR1"."MVIEW1"                           142.0 KB   10003行がインポートされました
オブジェクト型SCHEMA_EXPORT/TABLE/COMMENTの処理中です
オブジェクト型SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINTの処理中です
オブジェクト型SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICSの処理中です
オブジェクト型SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICSの処理中です
オブジェクト型SCHEMA_EXPORT/STATISTICS/MARKERの処理中です
オブジェクト型SCHEMA_EXPORT/MATERIALIZED_VIEWの処理中です
ORA-39083: オブジェクト型MATERIALIZED_VIEW:"MVUSR1"."MVIEW1"の作成が次のエラーで失敗しました:
ORA-06550: 行1、列9:
PLS-00352: 別のデータベースLINK1にアクセスできません。
ORA-06550: 行1、列9:
PLS-00201: 識別子SYS@LINK1を宣言してください。
ORA-06550: 行1、列9:
PL/SQL: Statement ignored

エラー文は次のとおりです:
CREATE MATERIALIZED VIEW "MVUSR1"."MVIEW1" ("C1", "C2") USING ("MVIEW1", (10, 'O19A', 1, 0, 0, "SCOTT", "MTEST1", '2019-12-02 00:45:49', 0, 76315, '1950-01-01 12:00:00', '', 0, 9022650, 0, NULL, (1, "C1", "C1", 0, 321, 0)), 2097249, 10, ('1950-01-01 12:00:00', 186, 0, 0, 9022650, 0, 0, 4194304, 1, NULL, NULL), '@"LINK1"') REFRESH FAST WITH PRIMARY KEY AS SELECT "MTEST1"."C1" "C1","MTEST1"."C2" "C2" FROM "MTEST1"@"LINK1" "MTEST1"

ジョブ"SYSTEM"."SYS_IMPORT_SCHEMA_01"が完了しましたが、1エラーが月 12月 2 01:26:25 2019 elapsed 0 00:00:42で発生しています
```

Data Pumpはエラー・メッセージを出力し、マテリアライズド・ビューは作成されませんでした。