史上最全Oracle資料泵常用命令

導讀：expdp和impdp是oracle資料庫之間移動資料的工具，本文簡單總結了資料泵的常用命令，希望對大家有幫助。

前言

expdp和impdp是oracle資料庫之間移動資料的工具。expdp和impdp只能在資料庫服務端使用，不能在客戶端使用。本文簡單總結了expdp和impdp常用的命令，詳細資訊參考oracle官方文件Utilities。

---

directory相關SQL語句:

select * from dba_directories;
create directory my_dir as '/home/oracle/tmp';
grant read,write on directory my_dir to scott;

EXPDP匯出

注意：
1、導數的資料庫使用者需要擁有對directory_object的讀寫許可權。
2、作業系統中需要已經存在directory_object指定的路徑。
3、oracle使用者擁有對directory_object指定路徑的讀寫許可權。
4、system使用者匯出使用者，會將建立使用者和授予系統許可權的後設資料也匯出，普通使用者不能匯出這些後設資料。

expdp命令示例

##匯出一張表，例：
expdp system/oracle directory=my_dir dumpfile=expdp.dmp   logfile=expdp.log tables=scott.emp
##匯出多張表，例：
expdp system/oracle directory=my_dir dumpfile=expdp.dmp   logfile=expdp.log tables=\(scott.emp,scott.dept\)
##匯出一個使用者(匯出這個使用者的所有物件)，例：
expdp system/oracle directory=my_dir dumpfile=expdp.dmp   logfile=expdp.log schemas=scott
##匯出多個使用者，例：
expdp system/oracle directory=my_dir dumpfile=expdp.dmp   logfile=expdp.log schemas=\(scott,hr\)
##匯出整個資料庫（sys、ordsys、mdsys的使用者資料不會被匯出）例：
expdp system/oracle directory=my_dir dumpfile=expdp.dmp   logfile=expdp.log full=yes

以匯出一個使用者為例

##並行匯出：
expdp system/oracle directory=my_dir dumpfile=expdp%U.dmp logfile=expdp.log schemas=scott parallel=5
##匯出使用者後設資料(包含表定義、儲存過程、函式等等):
expdp system/oracle directory=my_dir dumpfile=expdp.dmp   logfile=expdp.log schemas=scott content=metadata_only
##匯出使用者儲存過程,例：
expdp system/oracle directory=my_dir dumpfile=expdp.dmp   logfile=expdp.log schemas=scott include=procedure
##匯出使用者函式和檢視，例：
expdp system/oracle directory=my_dir dumpfile=expdp.dmp   logfile=expdp.log schemas=scott include=\(function,view\)
##匯出一個使用者，但不包括索引，例：
expdp system/oracle directory=my_dir dumpfile=expdp.dmp   logfile=expdp.log schemas=scott exclude=index

expdp引數說明:

attach=[schema_name.]job_name
說明：nodefault。連線到作業，進入互動模式。
匯出模式，以下五個引數互斥。
full=[yes|no]
說明：nodefault。匯出所有資料和後設資料。要執行完全匯出，需要具有datapump_exp_full_database角色。
schemas=schema_name[,...]
說明：default current user's schema。匯出使用者。
tables=[schema_name.]table_name[:partition_name][,...]
說明：nodefault。匯出表。
tablespaces=tablespace_name[,...]
說明：nodefault。匯出表空間。
transport_tablespaces=tablespace_name[,...]
說明：nodefault。匯出可移動表空間。
過濾條件，以下三個引數互斥:
query=[schema.][table_name:] query_clause
說明：nodefault。按查詢條件匯出。
exclude=object_type[:name_clause][,...]
說明：nodefault。排除特定的物件型別。
include=object_type[:name_clause][,...]
說明：nodefault。包括特定的物件型別。
其他引數:
directory=directory_object
說明：default:data_pump_dir。匯出路徑。
dumpfile=[directory_object:]file_name[,...]
說明：default:expdat.dmp。匯出的檔名。
logfile=[directory_object:]file_name
說明：default:export.log。匯出的日誌檔名。
content=[all|data_only|metadata_only]
說明：default:all。指定要匯出的資料。
parallel=integer
說明：default:1。並行度，該值應小於等於dmp檔案數量，或可以為'dumpfile='使用替換變數'%U'。
         RAC環境中，並行度大於1時，注意目錄應該為共享目錄。
compression=[all|data_only|metadata_only|none]
說明：default:metadata_only。壓縮。
parfile=[directory_path]file_name
說明：nodefault。指定匯出引數檔名稱。
network_link=source_database_link
說明：nodefault。連線到源資料庫進行匯出。
filesize=integer[b|kb|mb|gb|tb]
說明：default:0不限制大小。指定每個dmp檔案的最大大小。
      如果此引數小於將要匯出的資料大小，將報錯ORA-39095。
job_name=jobname_string
說明：default:system-generated name of the form SYS_EXPORT_<mode>_NN。指定job名稱。
version=[compatilble|latest|version_string]
說明：default:compatible。預設相容模式，可以指定匯出dmp檔案的版本。

IMPDP匯入

注意：
1、expdp匯出的檔案不能使用imp匯入，只能透過impdp匯入資料庫。
2、匯入時遇到已存在的物件，預設會跳過這個物件，繼續匯入其他物件。
3、匯入時應確認dmp檔案和目標資料庫的tablespace、schema是否對應。
4、匯入dmp檔案時，應確定dmp檔案匯出時的命令，以便順利匯入資料。
拿到一個dmp檔案，如果忘記了匯出命令，可以透過以下方法確認(非官方，生產資料勿使用)：

確認dmp檔案是exp匯出還是expdp匯出 1）xxd test.dmp | more

expdp匯出的檔案開頭為0301，exp匯出的檔案開頭為0303

2）strings  test.dmp | more

expdp匯出的dmp檔案頭資訊：
"SYS"."SYS_EXPORT_TABLE_01"  -----job名稱
x86_64/Linux 2.4.xx   -----作業系統版本
bjdb  -----資料庫名稱
ZHS16GBK  -----資料庫字符集
11.02.00.04.00  -----資料庫版本
exp匯出的dmp檔案頭資訊：
iEXPORT:V11.02.00  -----版本
USCOTT  -----使用者
RTABLES  -----物件
確認expdp匯出的dmp檔案的匯出命令
strings test.dmp | grep CLIENT_COMMAND

impdp命令示例

##匯入dmp檔案中的所有資料，例：
impdp system/oracle directory=my_dir dumpfile=expdp.dmp   logfile=impdp.log full=yes
##匯入一張表，例：
impdp system/oracle directory=my_dir dumpfile=expdp.dmp   logfile=impdp.log tables=scott.emp
##匯入多張表，例：
impdp system/oracle directory=my_dir dumpfile=expdp.dmp   logfile=impdp.log tables=\(scott.emp,scott.dept\)
##匯入一個使用者，例：
impdp system/oracle directory=my_dir dumpfile=expdp.dmp   logfile=impdp.log schemas=scott
##匯入多個使用者，例：
impdp system/oracle directory=my_dir dumpfile=expdp.dmp   logfile=impdp.log schemas=\(scott,hr\)

以匯入dmp檔案中的所有資料為例

##並行匯入：
impdp system/oracle directory=my_dir dumpfile=expdp%U.dmp logfile=impdp.log parallel=5
##匯入後設資料(包含表定義、儲存過程、函式等等):
impdp system/oracle directory=my_dir dumpfile=expdp.dmp   logfile=impdp.log content=metadata_only
##匯入儲存過程,例：
impdp system/oracle directory=my_dir dumpfile=expdp.dmp   logfile=impdp.log include=procedure
##匯入函式和檢視，例：
impdp system/oracle directory=my_dir dumpfile=expdp.dmp   logfile=impdp.log include=\(function,view\)
##匯入資料，但不包括索引，例：
impdp system/oracle directory=my_dir dumpfile=expdp.dmp   logfile=impdp.log exclude=index
##重新命名錶名匯入，例：
impdp system/oracle directory=my_dir dumpfile=expdp.dmp   logfile=impdp.log remap_table=scott.emp:emp1
##重新命名schema名匯入，例：	
impdp system/oracle directory=my_dir dumpfile=expdp.dmp   logfile=impdp.log remap_schema=scott:tim
##重新命名錶空間名匯入，例：
impdp system/oracle directory=my_dir dumpfile=expdp.dmp   logfile=impdp.log remap_tablespace=users:apptbs
##匯入時，忽略所有物件的段屬性，這樣匯入時物件都建立在目標資料庫使用者預設的表空間上。
impdp system/oracle directory=my_dir dumpfile=expdp.dmp   logfile=impdp.log transform=segment_attributes:n
##將dmp檔案的ddl語句匯入到一個檔案，不匯入資料庫，例：
impdp system/oracle directory=my_dir dumpfile=expdp.dmp   logfile=impdp.log sqlfile=import.sql

impdp引數說明

attach=[schema_name.]job_name
說明：nodefault。連線到作業，進入互動模式。
匯入模式，以下五個引數互斥。
full=[yes|no]
說明：default:yes。匯入dmp檔案的所有資料和後設資料。
schemas=schema_name[,...]
說明：nodefault。匯入使用者。
tables=[schema_name.]table_name[:partition_name][,...]
說明：nodefault。匯入表。
tablespaces=tablespace_name[,...]
說明：nodefault。匯入表空間。
transport_tablespaces=tablespace_name[,...]
說明：nodefault。匯入可移動表空間。
過濾條件，以下三個引數互斥:
query=[schema.][table_name:] query_clause
說明：nodefault。按查詢條件匯入。
exclude=object_type[:name_clause][,...]
說明：nodefault。排除特定的物件型別。
include=object_type[:name_clause][,...]
說明：nodefault。包括特定的物件型別。
其他引數:
directory=directory_object
說明：default:data_pump_dir。匯入路徑。
dumpfile=[directory_object:]file_name[,...]
說明：default:expdat.dmp。匯入的檔名。
logfile=[directory_object:]file_name
說明：default:export.log。匯入的日誌檔名。
content=[all|data_only|metadata_only]
說明：default:all。指定要匯入的資料。
parallel=integer
說明：default:1。並行度，該值應小於等於dmp檔案數量，或可以為'dumpfile='使用替換變數'%U'。
compression=[all|data_only|metadata_only|none]
說明：default:metadata_only。壓縮。
parfile=[directory_path]file_name
說明：nodefault。指定匯入引數檔名稱。
network_link=source_database_link
說明：nodefault。連線到源資料庫進行匯入。
job_name=jobname_string
說明：default:system-generated name of the form SYS_EXPORT_<mode>_NN。指定job名稱。
version=[compatilble|latest|version_string]
說明：default:compatible。預設相容模式，可以指定匯入dmp檔案的版本。
REMAP_TABLE=[schema.]old_tablename[.partition]:new_tablename
說明：nodefault。允許匯入期間重新命名錶名。
REMAP_SCHEMA=source_schema:target_schema
說明：nodefault。允許匯入期間重新命名schema名。
REMAP_TABLESPACE=source_tablespace:target_tablespace
說明：nodefault。允許匯入期間重新命名錶空間名。
TRANSFORM = transform_name:value[:object_type]
說明：nodefault。允許改正正在匯入的物件的DDL。
SQLFILE=[directory_object:]file_name
說明：nodefault。根據其他引數，將所有的 SQL DDL 寫入指定的檔案。
TABLE_EXISTS_ACTION=[SKIP | APPEND | TRUNCATE | REPLACE]
說明：default:skip(if content=data_only is specified,then the default is append)

互動模式

進入互動可以操作匯入匯出作業。

進入互動模式的方法：
1、匯入匯出命令列執行期間按Ctrl + c
2、expdp attach=jobname或impdp attach=jobnam 檢視匯入匯出日誌可以看到jobname，也可以透過查詢dba_datapump_jobs找到jobname。

報錯總結

系統目錄未建立，報錯：

ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 536
ORA-29283: invalid file operation

impdp匯入exp匯出的dmp檔案，報錯：

ORA-39000: bad dump file specification
ORA-39143: dump file "/u01/20161031/bjh02.dmp" may be an original export dump file

如果匯出的資料庫版本比匯入的資料版本高，需要在匯出時加上引數version=要匯入的資料庫版本。否則報錯：

ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-31640: unable to open dump file "/home/oracle/EXPDP20161024_1.DMP" for read
ORA-27037: unable to obtain file status

文末再附加一條匯出使用者後設資料的sqlfile命令，這個是之前一個朋友問我的，因為之前做的db2的工作，最近才開始和oracle打交道，資料泵瞭解的很少，所以被問到這塊的時候猶豫了片刻，第一個想到的自然是使用oracle自帶的資料泵工具expdp：

expdp system/oracle schemas=scott directory=my_dir dumpfile=scott.dmp logfile=scott.log content=metadata_only

但是問題來了，朋友要的是sqlfile不是dumpfile，經過百度得知可以透過plsql developer工具把後設資料匯出成sqlfile的形式，但是匯出的sqlfile還是不符合朋友的需求，後經指點得知，在匯出後設資料之後，只需要在匯入的時候加上sqlfile引數，就可以生成sqlfile檔案，具體命令如下：

impdp system/oracle directory=my_dir schemas=scott dumpfile=scott.dmp logfile=scott_imp.log sqlfile=scott.sql

impdp工具裡對sqlfile的描述如下

[oracle@Yukki tmp]$ impdp -help
SQLFILE
Write all the SQL DDL to a specified file.

將所有的 SQL DDL 寫入指定的檔案。

出處：墨天輪（，複製到網頁中開啟或者點選“閱讀原文”）