1. データベース管理者のための主要20c新機能の学習
  2. データベース管理者のための主要20c新機能の学習
  3. アップグレード、パッチ適用および移行
  4. Oracle Databaseユーティリティ
  5. Oracle Data Pumpでの同一操作への包含/除外
  6. 演習: エクスポートまたはインポートに対するオブジェクトの包含および除外

演習: エクスポートまたはインポートに対するオブジェクトの包含および除外

この演習では、同じ操作中にオブジェクトを含めたり除外して、オブジェクトをエクスポートまたはインポートする方法を示します。

  1. 新しい演習を開始する前に、演習の環境の推奨事項を参照してください。
  2. /home/oracle/labs/M104780GC10/create_PDB20_2.shシェル・スクリプトを使用して、PDB20_2にPDB20_2 PDBおよびHRユーザーを作成します。
    $ cd /home/oracle/labs/M104780GC10
$ /home/oracle/labs/M104780GC10/create_PDB20_2.sh
SQL*Plus: Release 20.0.0.0.0 - Production on Tue Mar 17 03:41:01 2020
Version 20.2.0.0.0

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

Connected to:
Oracle Database 20c Enterprise Edition Release 20.0.0.0.0 - Production
Version 20.2.0.0.0

SQL> ALTER SESSION SET db_create_file_dest='/home/oracle/labs';

Session altered.

SQL> ALTER PLUGGABLE DATABASE pdb20_2 CLOSE;

Pluggable database altered.

SQL> DROP PLUGGABLE DATABASE pdb20_2 INCLUDING DATAFILES;

Pluggable database dropped.

SQL>
SQL> CREATE PLUGGABLE DATABASE pdb20_2
  2      ADMIN USER pdb_admin IDENTIFIED BY password ROLES=(CONNECT)
  3          DEFAULT TABLESPACE users DATAFILE SIZE 1M AUTOEXTEND ON NEXT 1M
  4      CREATE_FILE_DEST='/home/oracle/labs';

Pluggable database created.

SQL> ALTER PLUGGABLE DATABASE pdb20_2 OPEN;

Pluggable database altered.

SQL> exit
Disconnected from Oracle Database 20c Enterprise Edition Release 20.0.0.0.0 - Production
Version 20.2.0.0.0

SQL*Plus: Release 20.0.0.0.0 - Production on Tue Mar 17 03:41:38 2020
Version 20.2.0.0.0

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

Connected to:
Oracle Database 20c Enterprise Edition Release 20.0.0.0.0 - Production
Version 20.2.0.0.0

SQL> DROP USER hr CASCADE;
DROP USER hr CASCADE
          *
ERROR at line 1:
ORA-01918: user 'HR' does not exist

SQL> CREATE USER hr IDENTIFIED BY password;

User created.

SQL> GRANT create session, create table, unlimited tablespace TO hr;

Grant succeeded.

SQL> CREATE DIRECTORY dp_dir AS '/home/oracle/labs';

Directory created.

SQL> GRANT read, write ON DIRECTORY dp_dir TO hr;

Grant succeeded.

SQL> EXIT
Disconnected from Oracle Database 20c Enterprise Edition Release 20.0.0.0.0 - Production
Version 20.2.0.0.0

SQL*Plus: Release 20.0.0.0.0 - Production on Tue Mar 17 03:41:39 2020
Version 20.2.0.0.0

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

Connected to:
Oracle Database 20c Enterprise Edition Release 20.0.0.0.0 - Production
Version 20.2.0.0.0

specify password for HR as parameter 1:

specify default tablespeace for HR as parameter 2:

specify temporary tablespace for HR as parameter 3:

specify log path as parameter 4:

PL/SQL procedure successfully completed.

User created.

ALTER USER hr DEFAULT TABLESPACE users
*
ERROR at line 1:
ORA-00959: tablespace 'USERS' does not exist

User altered.

Grant succeeded.

Grant succeeded.

Session altered.

Session altered.

Session altered.

******  Creating REGIONS table ....

Table created.

Index created.

Table altered.

******  Creating COUNTRIES table ....

Table created.

Table altered.

******  Creating LOCATIONS table ....

Table created.

Index created.

Table altered.

Sequence created.

******  Creating DEPARTMENTS table ....

Table created.

Index created.

Table altered.

Sequence created.

******  Creating JOBS table ....

Table created.

Index created.

Table altered.

******  Creating EMPLOYEES table ....

Table created.

Index created.

Table altered.

Table altered.

Sequence created.

******  Creating JOB_HISTORY table ....

Table created.

Index created.

Table altered.

******  Creating EMP_DETAILS_VIEW view ...

View created.

Commit complete.

Session altered.

******  Populating REGIONS table ....

1 row created.

1 row created.

1 row created.

1 row created.

******  Populating COUNTIRES table ....

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

******  Populating LOCATIONS table ....

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

******  Populating DEPARTMENTS table ....

Table altered.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

******  Populating JOBS table ....

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

******  Populating EMPLOYEES table ....

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

******  Populating JOB_HISTORY table ....

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

1 row created.

Table altered.

Commit complete.

Index created.

Index created.

Index created.

Index created.

Index created.

Index created.

Index created.

Index created.

Index created.

Index created.

Index created.

Commit complete.

Procedure created.

Trigger created.

Trigger altered.

Procedure created.

Trigger created.

Commit complete.

Comment created.

Comment created.

Comment created.

Comment created.

Comment created.

Comment created.

Comment created.

Comment created.

Comment created.

Comment created.

Comment created.

Comment created.

Comment created.

Comment created.

Comment created.

Comment created.

Comment created.

Comment created.

Comment created.

Comment created.

Comment created.

Comment created.

Comment created.

Comment created.

Comment created.

Comment created.

Comment created.

Comment created.

Comment created.

Comment created.

Comment created.

Comment created.

Comment created.

Comment created.

Comment created.

Comment created.

Comment created.

Comment created.

Comment created.

Commit complete.

PL/SQL procedure successfully completed.

SQL> Disconnected from Oracle Database 20c Enterprise Edition Release 20.0.0.0.0 - Production
Version 20.2.0.0.0
$
  3. 統計を除外して2つのHR表をエクスポートする前に、2つのHR表に統計が収集されていることを確認し、エクスポート・ダンプファイル用のディレクトリを作成します。
    1. 2つのHR表に統計が収集されていることを確認します。
      $ sqlplus system@PDB20

SQL*Plus: Release 20.0.0.0.0 - Production on Tue Mar 17 02:24:54 2020
Version 20.2.0.0.0

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

Enter password: password
Last Successful login time: Tue Mar 17 2020 02:23:18 +00:00

Connected to:
Oracle Database 20c Enterprise Edition Release 20.0.0.0.0 - Production
Version 20.2.0.0.0

SQL> SELECT num_rows FROM dba_tables WHERE table_name IN ('JOBS','DEPARTMENTS');

  NUM_ROWS
----------
        27
        19

SQL>
    2. エクスポート・ダンプファイル用のディレクトリを作成します。
      SQL> CREATE DIRECTORY dp_dir AS '/home/oracle/labs';

Directory created.

SQL> GRANT read, write ON DIRECTORY dp_dir TO hr;

Grant succeeded.

SQL> EXIT
$
  4. 統計を除外してPDB20から2つのHR表をエクスポートします。
    $ expdp hr@PDB20 DUMPFILE=hr.dmp DIRECTORY=dp_dir INCLUDE=TABLE:\"IN \(\'JOBS\',\'DEPARTMENTS\'\)\" EXCLUDE=STATISTICS REUSE_DUMPFILES=YES
Version 20.2.0.0.0

Copyright (c) 1982, 2020, Oracle and/or its affiliates.  All rights reserved.
Password: password

Connected to: Oracle Database 20c Enterprise Edition Release 20.0.0.0.0 - Production
Starting "HR"."SYS_EXPORT_SCHEMA_01":  hr/********@PDB20 DUMPFILE=hr.dmp DIRECTORY=dp_dir INCLUDE=TABLE:"IN ('JOBS','DEPARTMENTS')" EXCLUDE=STATISTICS REUSE_DUMPFILES=YES
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
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/CONSTRAINT/REF_CONSTRAINT
. . exported "HR"."JOBS"                                 7.109 KB      19 rows
. . exported "HR"."DEPARTMENTS"                          7.125 KB      27 rows
Master table "HR"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for HR.SYS_EXPORT_SCHEMA_01 is:
  /home/oracle/labs/hr.dmp
Job "HR"."SYS_EXPORT_SCHEMA_01" successfully completed at Tue Mar 17 02:30:24 2020 elapsed 0 00:00:18
$
  5. ダンプファイルを別のPDB (CDB20のPDB20_2)にインポートします。
    $ impdp system@PDB20_2 DUMPFILE=hr.dmp DIRECTORY=DP_DIR FULL=Y

Import: Release 20.0.0.0.0 - Production on Tue Mar 17 04:03:25 2020
Version 20.2.0.0.0

Copyright (c) 1982, 2020, Oracle and/or its affiliates.  All rights reserved.
Password: password

Connected to: Oracle Database 20c Enterprise Edition Release 20.0.0.0.0 - Production
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/********@PDB20_2 DUMPFILE=hr.dmp DIRECTORY=DP_DIR FULL=Y
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "HR"."JOBS"                                 7.109 KB      19 rows
. . imported "HR"."DEPARTMENTS"                          7.125 KB      27 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/CONSTRAINT/REF_CONSTRAINT
ORA-39083: Object type REF_CONSTRAINT:"HR"."DEPT_LOC_FK" failed to create with error:
ORA-00942: table or view does not exist

Failing sql is:
ALTER TABLE "HR"."DEPARTMENTS" ADD CONSTRAINT "DEPT_LOC_FK" FOREIGN KEY ("LOCATION_ID") REFERENCES "HR"."LOCATIONS" ("LOCATION_ID") ENABLE

ORA-39083: Object type REF_CONSTRAINT:"HR"."DEPT_MGR_FK" failed to create with error:
ORA-00942: table or view does not exist

Failing sql is:
ALTER TABLE "HR"."DEPARTMENTS" ADD CONSTRAINT "DEPT_MGR_FK" FOREIGN KEY ("MANAGER_ID") REFERENCES "HR"."EMPLOYEES" ("EMPLOYEE_ID") ENABLE
...
Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with 19 error(s) at Tue Mar 17 04:03:37 2020 elapsed 0 00:00:05
$
  6. 他のHR表を参照する制約を必要とするHR.DEPARTMENTSについて制約が欠落しているため、このインポートはエラーで完了します。統計および制約を除外してエクスポート操作を再実行します。
    $ expdp hr@PDB20 DUMPFILE=hr.dmp DIRECTORY=dp_dir INCLUDE=TABLE:\"IN \(\'JOBS\',\'DEPARTMENTS\'\)\" EXCLUDE=STATISTICS,CONSTRAINT REUSE_DUMPFILES=YES

Export: Release 20.0.0.0.0 - Production on Tue Mar 17 04:05:57 2020
Version 20.2.0.0.0

Copyright (c) 1982, 2020, Oracle and/or its affiliates.  All rights reserved.
Password: password

Connected to: Oracle Database 20c Enterprise Edition Release 20.0.0.0.0 - Production
Starting "HR"."SYS_EXPORT_SCHEMA_01":  hr/********@PDB20 DUMPFILE=hr.dmp DIRECTORY=dp_dir INCLUDE=TABLE:"IN ('JOBS','DEPARTMENTS')" EXCLUDE=STATISTICS,CONSTRAINT REUSE_DUMPFILES=YES
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
. . exported "HR"."JOBS"                                 7.109 KB      19 rows
. . exported "HR"."DEPARTMENTS"                          7.125 KB      27 rows
Master table "HR"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for HR.SYS_EXPORT_SCHEMA_01 is:
  /home/oracle/labs/hr.dmp
Job "HR"."SYS_EXPORT_SCHEMA_01" successfully completed at Tue Mar 17 04:06:15 2020 elapsed 0 00:00:14
$

    注意:

    インポートで制約に関連するエラーが発行されていないことに注目します。HR.DEPARTMENTS表に追加されている必要がある制約は除外されました。
  7. HR.JOBS表およびHR.DEPARTMENTS表の統計も除外されたことを確認します。
    $ sqlplus system@PDB20_2

Enter password: password

SQL> SELECT num_rows FROM dba_tables WHERE table_name IN ('JOBS','DEPARTMENTS');

no  rows selected

SQL> EXIT
$

親トピック: Oracle Data Pumpでの同一操作への包含/除外