Oracle Export/Import

Introduction

There are primarily two types of export/import tools for Oracle:

• exp（Export） / imp（Import）
• expdp（Data Pump Export） / impdp（Data Pump Import）

exp / imp are older tools, while expdp / impdp are newer export/import tools added since Oracle 10g. The file formats of the two are not compatible, and expdp / impdp are currently recommended. However, exp / imp are still used in practice.

While exp / imp allowed specifying any local folder on a PC, expdp / impdp always require using an Oracle directory object. This makes exp / imp seem more convenient. If you are not a DB administrator, you might even need to request the creation of a directory object. This difference arises from the fact that traditional exp / imp operate on the client side, whereas expdp / impdp operate on the server side. Because expdp / impdp run on the server, they offer advantages like faster processing and parallelization.

This memo will simply organize the commonly used forms of both tools.

exp (Export)

exp ユーザー名/パスワード file=ダンプファイル名 owner=スキーマ名 statistics=none

Example:

exp HOGE/hoge file=HOGE_20220921_1431.dmp owner=HOGE statistics=none

d

• statistics=none means "do not export statistics." In most cases, statistics are re-collected at the import destination, so it's generally common to omit them by setting statistics=none.

imp (Import)

imp ユーザー名/パスワード file=ダンプファイル名 fromuser=インポート元のスキーマ touser=インポート先のスキーマ ignore=y

fromuser and touser can be a bit confusing:

• fromuser: The original schema name stored in the dump file (the owner during export). If you specify a schema that does not exist in the dump file, nothing will be imported.
• touser: The destination schema name for import (where the data will be placed). The destination schema must be created beforehand. Specifying a non-existent schema will result in an error.

Example:

imp HOGE_20220921/hoge file=HOGE_20220921_1431.dmp fromuser=HOGE touser=HOGE_20220921 ignore=y

expdp (Data Pump Export)

expdp ユーザー名/パスワード DUMPFILE=ダンプファイル名 SCHEMAS=スキーマ名

Example:

expdp Hoge/hoge DUMPFILE=HOGE_20220921_1531.dmp SCHEMAS=Hoge

The dump file is created in the folder associated with the directory object. You cannot specify subdirectories.

The folder for the default directory object (DATA_PUMP_DIR) can be retrieved using the following SQL:

SELECT * FROM ALL_DIRECTORIES
WHERE DIRECTORY_NAME = 'DATA_PUMP_DIR';

OWNER	DIRECTORY_NAME	DIRECTORY_PATH	ORIGIN_CON_ID
SYS	DATA_PUMP_DIR	C:\app\oracle\admin\orcl\dpdump\	0

If you want to output to a folder other than the default, create a directory object and specify it with the directory option.

impdp (Data Pump Import)

Similar to expdp, the dump file must be placed in the folder where the directory object is defined.

By default, impdp tries to import into a tablespace with the same name as the one during export. If no such tablespace exists, it will result in an error, so you need to explicitly specify the tablespace (export tablespace:import destination tablespace).

impdp ユーザー名/パスワード dumpfile=ダンプファイル名 schemas=エクスポート時のスキーマ remap_schema=エクスポート時のスキーマ:インポート先のスキーマ table_exists_action=replace remap_tablespace=エクスポート時の表領域:インポート先の表領域

Example:

impdp HOGE_20220921/hoge dumpfile=HOGE_20220921_1531.DMP schemas=HOGE remap_schema=HOGE:HOGE_20220921 table_exists_action=replace remap_tablespace=HOGE_TABLESPACE:USERS

When ORA-39358 Occurs with impdp

C:\hoge>impdp HOGE_20220921/hoge dumpfile=HOGE_20220921_1531.DMP schemas=HOGE remap_schema=HOGE:HOGE_20220921 table_exists_action=replace remap_tablespace=HOGE_TABLESPACE:USERS

Import: Release 12.2.0.1.0 - Production on 木 10月 23 14:30:55 2025

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
ORA-39002: invalid operation
ORA-39358: Export dump file version 19.0.0.0.0 is not compatible with target version 12.2.0

This error indicates that a dump file exported from a higher version cannot be imported into a lower version. A Google search often suggests the solution "specify VERSION when using expdp," but there are cases where expdp cannot be performed again.

Actually, you can import by specifying the higher version with the VERSION option in impdp. Specify the following version:

impdp HOGE_20220921/hoge dumpfile=HOGE_20220921_1531.DMP schemas=HOGE remap_schema=HOGE:HOGE_20220921 table_exists_action=replace remap_tablespace=HOGE_TABLESPACE:USERS VERSION=19

This merely skips the version check, so data with incompatible data types between versions may not be importable. However, in cases where no special data types are involved, data can usually be imported successfully. Try it if you encounter this issue.