Oracle數據泵常用的命令整理

本篇內容介紹了“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文件的版本。

cluster=[yes|no]

說明：default:yes。Utilize cluster resources and distribute workers across the Oracle RAC。需要特別注意的是，當處于多節點環境下時，若導出目錄不在共享存儲上，不添加cluster=no參數將會報ORA-31693等錯誤，原因是其他節點對導出目錄無權限。

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=jobname

     查看導入導出日志可以看到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 directory=my_dir schemas=scott 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 寫入指定的文件。

“Oracle數據泵常用的命令整理”的內容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業相關的知識可以關注億速云網站，小編將為大家輸出更多高質量的實用文章！