またも oracleをあまり使用したことがない自分用メモ。

1. 旧 exp/imp と新 expdp/impdp

以前からの exp/imp は下位互換の為に残されていますので、 oracle 10g から導入のexpdp/impdp を利用すべきようです。

exp/imp や expdp/impdp は、oracle のinstall先である /u01/app/oracle/product/12.2.0/dbhome_1/bin に存在します

2. exp & imp によるエクスポート & インポート

exp によるエクスポート

まず「exp help=y」コマンドでヘルプを見ると、 expは圧縮データをエクスポートすること等が分かります。

$ /u01/app/oracle/product/12.2.0/dbhome_1/bin/exp help=y
Export: Release 12.2.0.1.0 - Production on Mon Dec 28 23:39:04 2020
  :
You can let Export prompt you for parameters by entering the EXP
command followed by your username/password:
     Example: EXP SCOTT/TIGER
Or, you can control how Export runs by entering the EXP command followed
by various arguments. To specify parameters, you use keywords:
     Format:  EXP KEYWORD=value or KEYWORD=(value1,value2,...,valueN)
     Example: EXP SCOTT/TIGER GRANTS=Y TABLES=(EMP,DEPT,MGR)
               or TABLES=(T1:P1,T1:P2), if T1 is partitioned table
USERID must be the first parameter on the command line.

Keyword    Description (Default)      Keyword      Description (Default)
--------------------------------------------------------------------------
USERID     username/password          FULL         export entire file (N)
BUFFER     size of data buffer        OWNER        list of owner usernames
FILE       output files (EXPDAT.DMP)  TABLES       list of table names
COMPRESS   import into one extent (Y) RECORDLENGTH length of IO record
GRANTS     export grants (Y)          INCTYPE      incremental export type
INDEXES    export indexes (Y)         RECORD       track incr. export (Y)
DIRECT     direct path (N)            TRIGGERS     export triggers (Y)
LOG        log file of screen output  STATISTICS   analyze objects (ESTIMATE)
ROWS       export data rows (Y)       PARFILE      parameter filename
CONSISTENT cross-table consistency(N) CONSTRAINTS  export constraints (Y)

$ /u01/app/oracle/product/12.2.0/dbhome_1/bin/exp system/ないしょ FILE=exp_data.dmp
Export: Release 12.2.0.1.0 - Production on Mon Dec 28 23:46:15 2020
   :
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)
   :
. . exporting table                         MEMBER          1 rows exported
EXP-00091: Exporting questionable statistics.
. . exporting table                            OL$
. . exporting table                       OL$HINTS
. . exporting table                       OL$NODES
. . exporting table        SQLPLUS_PRODUCT_PROFILE          0 rows exported
EXP-00091: Exporting questionable statistics.
. . exporting table                     TEST_TBL_1      10000 rows exported
       :
. . exporting table                     TEST_TBL_4
. . exporting partition             TBL_4_PART__500000     499999 rows exported
. . exporting partition             TBL_4_PART_1000000     500000 rows exported
      :
. . exporting partition             TBL_4_PART_5500000          1 rows exported
EXP-00091: Exporting questionable statistics.
  :
$

一旦、テーブルをいくつか削除

$ sqlplus /nolog

SQL> connect system/ないしょ;

SQL> column OWNER      format a10
column TABLE_NAME format a10
SELECT OWNER,TABLE_NAME
FROM   ALL_TABLES
WHERE OWNER='SYSTEM' and TABLE_NAME like 'TEST_%'
ORDER BY OWNER,TABLE_NAME;

OWNER      TABLE_NAME
---------- ----------
SYSTEM     TEST_TBL_1
SYSTEM     TEST_TBL_2
SYSTEM     TEST_TBL_3
SYSTEM     TEST_TBL_4


SQL> drop table TEST_TBL_1 cascade constraints;
Table dropped.

SQL> drop table TEST_TBL_2 cascade constraints;
Table dropped.

SQL> commit;
Commit complete.

imp によるインポート

expは圧縮した状態でエクスポートしていると思いますが、 user=systemで実施した為か、269MBサイズがあります。

$ ls -lh exp_data.dmp 
-rw-r--r-- 1 oracle oinstall 269M Dec 28 23:46 exp_data.dmp

$ /u01/app/oracle/product/12.2.0/dbhome_1/bin/imp system/ないしょ FILE=exp_data.dmp

Import: Release 12.2.0.1.0 - Production on Tue Dec 29 00:12:17 2020

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


Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

Export file created by EXPORT:V12.02.00 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
IMP-00403: 

Warning: This import generated a separate SQL file "import_sys"
   which contains DDL that failed due to a privilege issue.

IMP-00031: Must specify FULL=Y or provide FROMUSER/TOUSER or TABLES arguments
IMP-00000: Import terminated unsuccessfully

上記のIMP-00031エラーとなった為、対象テーブルを先程、dropしたテーブルに限定します。

$ /u01/app/oracle/product/12.2.0/dbhome_1/bin/imp system/ないしょ \
>         FILE=exp_data.dmp TABLES=TEST_TBL_1,TEST_TBL_2

Import: Release 12.2.0.1.0 - Production on Tue Dec 29 00:15:36 2020
   :
Export file created by EXPORT:V12.02.00 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
   :
. . importing table                   "TEST_TBL_1"      10000 rows imported
. . importing table                   "TEST_TBL_2"    1000000 rows imported
Import terminated successfully with warnings.
$

最後にインポート結果を確認して完了です。

$ sqlplus /nolog

SQL> connect system/ないしょ;

SQL> column OWNER      format a10
column TABLE_NAME format a10
SELECT OWNER,TABLE_NAME
FROM   ALL_TABLES
WHERE OWNER='SYSTEM' and TABLE_NAME like 'TEST_%'
ORDER BY OWNER,TABLE_NAME;

OWNER      TABLE_NAME
---------- ----------
SYSTEM     TEST_TBL_1
SYSTEM     TEST_TBL_2
SYSTEM     TEST_TBL_3
SYSTEM     TEST_TBL_4

SQL> select count(*) from TEST_TBL_1;
  COUNT(*)
----------
     10000

SQL> select count(*) from TEST_TBL_2;
  COUNT(*)
----------
   1000000
SQL>

3. expdp & impdp によるエクスポート & インポート

expdp によるエクスポート

先程の expと同様「expdp help=y」コマンドでヘルプを見ることができますが、 expdp はオプションが増えすぎて、見る気をなくします。

$ /u01/app/oracle/product/12.2.0/dbhome_1/bin/expdp help=y
  :
Export: Release 12.2.0.1.0 - Production on Tue Dec 29 00:25:09 2020

The Data Pump export utility provides a mechanism for transferring data objects
between Oracle databases. The utility is invoked with the following command:

   Example: expdp scott/tiger DIRECTORY=dmpdir DUMPFILE=scott.dmp

You can control how Export runs by entering the 'expdp' command followed
by various parameters. To specify parameters, you use keywords:

   Format:  expdp KEYWORD=value or KEYWORD=(value1,value2,...,valueN)
   Example: expdp scott/tiger DUMPFILE=scott.dmp DIRECTORY=dmpdir SCHEMAS=scott
               or TABLES=(T1:P1,T1:P2), if T1 is partitioned table
   :
＜略 この先が長い＞

インターネットで検索すると、expdp コマンドには、 DIRECTORYオプションを指定するよう記載されているページを見かけますが、 DIRECTORYオプションがなくても、 /u01/app/oracle/admin/orcl/dpdump/expdp_data.dmp へエクスポートできました。

$ /u01/app/oracle/product/12.2.0/dbhome_1/bin/expdp system/ないしょ DUMPFILE=expdp_data.dmp

Export: Release 12.2.0.1.0 - Production on Tue Dec 29 00:31:32 2020
   :
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01":  system/********
    dumpfile=expdp_data.dmp reuse_dumpfiles=true nologfile=true 
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
   :
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
. . exported "SYSTEM"."TEST_TBL_3"                       112.9 MB 5000000 rows
. . exported "SYSTEM"."TEST_TBL_2"                       21.81 MB 1000000 rows
. . exported "SYSTEM"."TEST_TBL_4":"TBL_4_PART_1500000"  11.39 MB  500000 rows
. . exported "SYSTEM"."TEST_TBL_4":"TBL_4_PART_2000000"  11.39 MB  500000 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/orcl/dpdump/expdp_data.dmp

一旦、テーブルをいくつか削除

詳細の記載は省略しますが、先程と同様、テーブルをいくつか削除します。

impdp によるインポート

先程のimpコマンドと同様のパラメータで、インポートできます

$ /u01/app/oracle/product/12.2.0/dbhome_1/bin/impdp system/ないしょ \
         FILE=expdp_data.dmp TABLES=TEST_TBL_1,TEST_TBL_2
Import: Release 12.2.0.1.0 - Production on Tue Dec 29 00:43:38 2020
   :
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
   :
Starting "SYSTEM"."SYS_IMPORT_TABLE_01":  system/********
   dumpfile=expdp_data.dmp TABLES=TEST_TBL_1,TEST_TBL_2 nologfile=true 
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "SYSTEM"."TEST_TBL_2"                       21.81 MB 1000000 rows
. . imported "SYSTEM"."TEST_TBL_1"                       219.9 KB   10000 rows
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/STATISTICS/MARKER
Job "SYSTEM"."SYS_IMPORT_TABLE_01" successfully completed at Tue Dec 29 00:43:59 2020 elapsed 0 00:00:20

end0tknr's kipple - web写経開発

太宰府天満宮の狛犬って、妙にカワイイ

oracle 12c のエクスポート/インポートツール - exp/imp と expdp/impdp

1. 旧 exp/imp と新 expdp/impdp

2. exp & imp によるエクスポート & インポート

exp によるエクスポート

一旦、テーブルをいくつか削除

imp によるインポート

3. expdp & impdp によるエクスポート & インポート

expdp によるエクスポート

一旦、テーブルをいくつか削除

impdp によるインポート

1. 旧 exp/imp と 新 expdp/impdp

2. exp & imp による エクスポート & インポート

exp によるエクスポート

一旦、テーブルをいくつか削除

imp によるインポート

3. expdp & impdp による エクスポート & インポート

expdp によるエクスポート

一旦、テーブルをいくつか削除

impdp によるインポート

1. 旧 exp/imp と新 expdp/impdp

2. exp & imp によるエクスポート & インポート

3. expdp & impdp によるエクスポート & インポート