LoginSignup
3

More than 3 years have passed since last update.

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

Last updated at Posted at 2019-12-02

マテリアライズド・ビューを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はエラー・メッセージを出力し、マテリアライズド・ビューは作成されませんでした。

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
3