Oracle資料庫expdp用法以及注意事項

一、匯出注意事項

檢查資料庫版本（用于決定匯出時生成為哪個版本的dmp頭檔案） select version from v$instance; 也可以用sqlplus -v 查看， 

檢查字符集是否一致（字符集不一致，不能匯入） select userenv('language') from dual; 

檢查資料量及磁盤空間（決定采取什么樣的方式匯出及匯入）

二、expdp和exp的區別

在10g之前,傳統的匯出和匯入分別使用EXP工具和IMP工具,從10g開始,不僅保留了原有的EXP和IMP工具,還提供了資料泵匯出匯入工具EXPDP和IMPDP：

1、EXP和IMP是客戶端工具程式,它們既可以在可以客戶端使用,也可以在服務端使用，

2、EXPDP和IMPDP是服務端的工具程式,他們只能在ORACLE服務端使用,不能在客戶端使用，客戶端只能用來連接服務器啟動匯入匯出操作，匯出檔案只存盤在服務端，

3、IMP只適用于EXP匯出檔案,不適用于EXPDP匯出檔案;IMPDP只適用于EXPDP匯出檔案,而不適用于EXP匯出檔案，

4、EXPDP/IMPDP在備份和恢復時間上要比EXP/IMP有著優勢，而且檔案要小的多，

5、使用EXPDP/IMPDP管理更加靈活，支持并行；可中斷；可以修改元資料檔案名字、表空間名；支持元資料匯入匯出；估算匯出資料所需空間；指定匯入資料庫版本等，

三、expdp使用方法

1、創建expdp匯出目錄，默認匯出目錄為D:\app\Administrator/admin/orcl/dpdump/（其中前面為Oracle安裝目錄），

create or replace directory 目錄名 as '目錄路徑'
--例：create or replace directory dmp as 'D:/app/dmp'

注意：目錄路徑必須是已經存在的目錄，如果目錄不存在，也會顯示目錄常見成功（陳述句不會自動去常見目錄），在執行expdp時候會報以下錯誤：

ORA-39002: 操作無效

ORA-39070: 無法打開日志檔案，

2、查看當前創建的所有dmp匯出目錄  (驗證看是否創建成功)

select * from dba_directories

3、為創建的目錄賦權限，需要以其他用戶運行，建議在sys用戶下執行，

grant read,write on directory 目錄名 to 需要賦值的用戶名
--例：grant read,write on directory dmp to GISDATA;

4、匯出（這里使用最簡單的匯出方式）

expdp 用戶名/密碼@資料庫實體名 directory=匯出目錄 dumpfile=匯出的檔案名.dmp logfile=匯出的日志名.log
--例：expdp GISDATA/GISDATA@LOCALHOST/orcl directory=dmp dumpfile=GISDATA20200921.dmp logfile=GISDATA20200921.log 

四、常用匯出命令

1、按用戶導
expdp GISDATA/GISDATA@localhost/orcl schemas=GISDATA dumpfile=expdp.dmp directory=dmp  logfile=expdlog.log;
2、并行行程parallel
expdp GISDATA/GISDATA@localhost/orcl directory=dmp dumpfile=expdp.dmp parallel=40 job_name=expdpjob logfile=expdlog.log
3、按表名導
expdp GISDATA/GISDATA@localhost/orcl TABLES=test1,test2 dumpfile=expdp.dmp directory=dmp  logfile=expdlog.log;
4、按查詢條件導
expdp GISDATA/GISDATA@localhost/orcl directory=dmp  dumpfile=expdp.dmp Tables=test query='WHERE id<20' logfile=expdlog.log;
5、按表空間導
expdp GISDATA/GISDATA directory=dmp  dumpfile=expdp.dmp TABLESPACES=GIDDATA,YWDATA logfile=expdlog.log;
6、導整個資料庫
expdp GISDATA/GISDATA directory=dmp  dumpfile=expdp.dmp FULL=y logfile=expdlog.log;

五、expdp引數說明

可以輸入expdp help=y命令查看其引數說明，

• ATTACH

作用
    當我們使用ctrl+C 退出互動式命令時，可心使用attach引數重新進入到互動模式
語法
    ATTACH=[schema_name.]job_name
    Schema_name用戶名,job_name任務名
示例
    Expdp scott/tiger ATTACH=scott.export_job

• CONTENT

作用
    限制了匯出的內容，包括三個級別：全部／資料／元資料（結構）
語法
   CONTENT={ALL | DATA_ONLY | METADATA_ONLY}
   ALL           -- 匯出所有資料，包括元資料及資料
   DATA_ONLY     -- 只匯出資料
   METADATA_ONLY -- 只包含元資料，就是創建陳述句
示例
   Expdp scott/tiger DIRECTORY=dump DUMPFILE=a.dump CONTENT=METADATA_ONLY

• DIRECTORY

作用

此路徑可以理解為實際絕對路徑在oracle資料庫里的別名，是匯出檔案的存盤位置
    路徑的創建： create directory &DIRECTORY_NAME AS '&PATH';
    查看已存在路徑： select  * from dba_directories;
語法
    directory=[directory_name]
示例
    Expdp scott/tiger DIRECTORY=dump_dir DUMPFILE=lhb.dump

• DUMPFILE

作用
    此引數用戶命名匯出檔案，默認是 expdat.dmp. 檔案的存盤位置如果在檔案名前沒有指定directory,則會默認存盤到directory引數指定的路徑下，
語法
    DUMPFILE=[dump_dir:]file_name
示例
    Expdp scott/tiger DIRECTORY=dump_dir DUMPFILE=dump_dir1:a.dmp

• ESTIMATE

在使用Expdp進行匯出時，Expdp需要計算匯出資料大小容量，Oracle可以通過兩種方式進行容量估算，一種是通過資料塊(blocks)數量、一種是通過統計資訊中記錄的內容(statistics)估算.

語法結構：
    EXTIMATE={BLOCKS | STATISTICS}
示例：
    Expdp scott/tiger TABLES=emp ESTIMATE=STATISTICS DIRECTORY=dump_dir DUMPFILE=halberd.dump
    Expdp scott/tiger TABLES=emp ESTIMATE=BLOCKS DIRECTORY=dump_dir DUMPFILE=halberd.dump

• EXTIMATE_ONLY

作用
    此引數用于統計匯出的資料量大小及統計程序耗時長短，
語法
    EXTIMATE_ONLY={Y | N}
示例
    Expdp scott/tiger ESTIMATE_ONLY=y NOLOGFILE=y directory=dump_dir schemas=halberd

• EXCLUDE

作用
    此引數用于排除不需要匯出的內容，如我們進行全庫匯出，但是不需要匯出用戶scott，此時需要在exlude后先指定排除型別為schema,再指定具體的schema，具體使用方法見include引數. EXCLUDE與include的使用方法是一樣的
語法
    EXCLUDE=object_type[:name_clause] [,object_type[:name_clause] ]
    name_clause
        "='object_name'"
        "in ('object_name'[,'object_name',....])"
        "in (select_clause) "
    Object_type物件型別，如：table,view,procedure,schema等
    name_clause指定名稱的陳述句,如果不具體指定是哪個物件，則此類所有物件都不匯出, select 陳述句中表名不要加用戶名，用戶名，通過schemas 指定，

示例
    expdp scott/tiger DIRECTORY=dump_dir DUMPFILE=halberd.dup EXCLUDE=VIEW
    expdp scott/tiger DIRECTORY=dump_dir DUMPFILE=halberd.dup EXCLUDE=TABLE:\" IN\(\'TEMP\',\'GRADE\'\)\"
    EXCLUDE=TABLE:"='APPLICATION_AUDIT'"

• FILESIZE

作用
    用于指定單個匯出的資料檔案的最大值，與％U一起使用，比如，我們需要匯出100G的資料，檔案全部存盤到一個檔案內，在檔案傳輸時，會耗費大量的時間，此時我們就可以使用這個引數，限制每個檔案的大小，在傳輸匯出檔案時，就可以多個檔案同時傳送，大大的節省了檔案傳輸時間，提高了作業的效率，
語法
  FILESIZE=integer[B | K | M | G]
示例
   Expdp scott/tiger DIRECTORY=dump_dir DUMPFILE=halberd%U.dup FILESIZE=20g

• FLASHBACK_SCN／FLASHBACK_TIME

作用
    基于undo 及scn號(時間點)進行的資料匯出，使用此引數設定會進行flashback query的功能，查詢到對應指定的SCN時的資料，然后進行匯出，只要UNDO不被覆寫，無論資料庫是否重啟，都可以進行匯出. flashback_time引數與flashback_scn的原理是一樣的，在匯出的資料里保持資料的一致性是很有必要的，這個，，我想，沒誰傻忽忽的把這兩個引數一起使用吧？所以我就不提醒你兩個引數不可以同時使用了，
語法
   FLASHBACK_SCN=scn_value
   FLASHBACK_TIME 有多種設定值的格式：
   flashback_time=to_timestamp (localtimestamp)
   flashback_time=to_timestamp_tz (systimestamp)
   flashback_time="TO_TIMESTAMP (""25-08-2003 14:35:00""， ""DD-MM-YYYY HH24:MI:SS"")"  使用此格式可能會遇到ORA-39150錯誤，
示例
   Expdp scott/tiger DIRECTORY=dump_dir DUMPFILE=halberd.dmp FLASHBACK_SCN= 12345567789
   Expdp scott/tiger DIRECTORY=dump_dir DUMPFILE=halberd.dmp FLASHBACK_TIME= to_timestamp (localtimestamp)

• FULL

作用
   指定匯出內容為全庫匯出，這里需要特別注意的是,expdp 不能匯出sys用戶物件，即使是全庫匯出也不包含sys用戶，
語法
   FULL={Y | N}
示例
   expdp \'\/ as sysdba\' directory=dump_dir full=y

• HELP

作用
    當我們對引數的意義不了解時，或者忘記引數怎么寫時，就可以用這個引數，來尋求幫助，實際上和作業系統里的man命令是一樣的，
示例
    impdp -help
    expdp help=y

• INCLUDE

作用
    限制范圍，指定自己想要的內容，比如要匯出某個用戶的某張表，
語法
    INCLUDE = object_type[:name_clause],object_type[:name_clause]
示例
    impdp dbmon/dbmon_123 directory=dump_dir network_link=zjzwb2 SCHEMAS=AICBS remap_schema=aicbs:aicbsb include=table:\"IN\(SELECT TABLE_NAME FROM dbmon.TABLES_TOBE_MASKED\)\"  LOGFILE=zjzwb.log transform=segment_attributes:n
    PARFILE中設定:
        INCLUDE=table:"in(select table_name from dba_tables where owner='AA')"
        INCLUDE=TABLE:"IN('TEST1','TEST2')"
    SHELL環境設定:
        INCLUDE=TABLE:\"IN\(SELECT TABLE_NAME FROM DBA_TABLES WHERE OWNER=\'AA\'\)\"
        INCLUDE=TABLE:\"IN\(\'TEST1\',\'TEST2\'\)\"
說明
    當匯入命令在目標端發起時，select 子句所涉及的表要在源端，并且dblink 所使用的用戶有訪問的權限，

• JOB_NAME

作用
    指定任務名，如果不指定的話，系統會默認自動命名：SYS_EXPORT_mode_nn
語法
    JOB_NAME=&JOB_NAME
其他
    查看有哪些expdp/impdp job，可以通過dba_datapump_jobs查看，其實你通過v$session.action也可以查看到
    大多與attach引數一起使用，重新進行expdp互動命令時使用，

• LOGFILE

作用： 指定匯出日志名稱，默認是：expdp.log
語法
    LOGFILE=[DIRECTORY:]file_name   , 如果引數值里沒有指定路徑，會默認使用directory引數值所指向的路徑，
    directory ： 存盤路徑,
    file_name :日志檔案名
示例
    expdp scott/tiger DIRECTORY=dump_dir DUMPFILE=halberd.dmp logfile=halberd.log
    impdp scott/tiger DIRECTORY=dump_dir DUMPFILE=halberd.dmp logfile=halberd.log

• NETWORK_LINK

作用
    此引數只有在匯入（impdp）時使用，可通過本地資料庫里的db_link連接到其他資料庫A，將資料庫A的資料直接匯入到本地資料庫，中間可節省匯出資料檔案，傳送資料檔案的程序，很方便，但是要特別注意，不同版本之間可能會存在問題，比如源庫為10g,目標庫為11g，使用network_link引數會報錯，至于 12C 與低版本之間是否有問題尚未嘗試，
語法
    network_link=[db_link]
示例
    impdp scott/tiger DIRECTORY=dump_dir DUMPFILE=halberd.dmp NETWORK_LINK=to_tjj SCHEMAS=halberd logfile=halberd.log

• NOLOGFILE

作用
    不寫匯入匯出日志，這個筆者是灰常灰常滴不建議設定為“Y”滴，
語法
    nologfile=[y|n]

• PARALLEL

作用
    指定匯出／匯入時使用多少個并發，默認是1.
語法
    parallel=[digit]
示例
    expdp \'\/ as sysdba\' directory=dump_dir schemas=halberd dumpfile=halberd%U.dmp parallel=8 logfile=halberd.log

• PARFILE

作用
    引數檔案，這個引數檔案里，存盤著一些引數的設定，比如上面說過的，parallel,network_link,等，匯出時，可以使用此引數，expdp/impdp會自動讀取檔案中的引數設定，進行操作，
語法
    PARFILE=[directory_path] file_name
示例
   expdp \'\/ as sysdba\' parfile=halberd.par
   cat halberd.par
   directory=dump_dir                          
   logfile=test.log                            
   schemas=test                                
   query="where create_date > last_day(add_months(sysdate,-1)) and create_date <= last_day(sysdate)" 

   transform=segment_attributes:n                 
   network_link=to_aibcrm

   table_exists_action=append                    
   impdp \'\/ as sysdba\' parfile=test.par

• QUERY

作用
    此引數指定在匯入匯出時的限制條件，和SQL陳述句中的 "where" 陳述句是一樣兒一樣兒滴
語法
    QUERY=([schema.] [table_name:] query_clause, [schema.] [table_name:] query_clause,……)
    CONTENT=METADATA_ONLY, EXTIMATE_ONLY＝Y,TRANSPORT_TABLESPACES.
示例
   Expdp scott/tiger directory=dump dumpfiel=a.dmp Tables=emp query="WHERE deptno<>20"

• SCHEMAS

作用
    指定匯出／匯入哪個用戶
語法
    schemas=schema_name[,schemaname,....]
示例
    expdp \'\/ as sysdba\' directory=dump_dir schemas=halberd

• REMAP_SCHEMA

 只在匯入時使用
作用
    當把用戶A的物件匯入到用戶（其實應該叫schema，將就看吧）B時，使用此引數，可實作要求
格式
    remap_schema=schema1: schema2
示例
    impdp \'\/ as sysdba\' directory=dump_dir dumpfile=halberd.dmp logfile=halberd.log remap_schema=scott:halberd

• TABLES

作用
    指定匯出哪些表，
格式
    TABLES=[schema.]table_name[:partition_name][,[schema.]table_name[:partition_name]]
說明
    Schema 表的所有者；table_name表名；partition_name磁區名.可以同時匯出不同用戶的不同的表
示例
    expdp \'\/ as sysdba\' directory=dump_dir tables=emp.emp_no,emp.dept

• TABLESPACES

作用
    指定匯出／匯入哪個表空間，
語法
    tablespaces=tablespace_name[,tablespace_name,....]
示例
    expdp \'\/ as sysdba\' directory=dump_dir tablespace=user

• REMAP_TABLESPACE

作用
    只有在匯入時使用，用于進行資料的表空間遷移， 把前一個表空間中的物件匯入到冒號后面的表空間
用法
    remap_tablespace=a:b
說明
   a: 資料所在的原表空間; b： 目標表空間
示例
   impdp \'\/ as sysdba\' directory=dump_dir tables=emp.dept remap_tablespace=user:user1

• TRANSPORT_FULL_CHECK

檢查需要進行傳輸的表空間與其他不需要傳輸的表空間之間的信賴關系，默認為N，當設定為“Y”時，會對表空間之間的信賴關系進行檢查，如A（索引表空間）信賴于B（表資料表空間），那么傳輸A而不傳輸B，則會出錯，相反則不會報錯，

• TRANSPORT_TABLESPACES

作用
    列出需要進行資料傳輸的表空間
格式
     TRANSPORT_TABLESPACES＝tablespace1[,tablespace2,.............]

• TRANSFORM

作用
  此引數只在匯入時使用，是一個用于設定存盤相關的引數,有時候也是相當方便的，假如資料對應的表空間都存在的話，就根本用不到這個引數，但是，假如資料存盤的表空間不存在，使用此引數匯入到用戶默認表空間就可以了，更靈活的，可以使用remap_tablespace引數來指定，
格式
    transform=transform_name:value[bject_type]
    transform_name = [OID | PCTSPACE | SEGMENT_ATTRIBUTES | STORAGE]:[Y|N]
    segment attributes:段屬性包括物理屬性、存盤屬性、表空間和日志,Y 值按斬訓出時的存盤屬性匯入，N時按照用戶、表的默認屬性匯入
    storage:默認為Y，只取物件的存盤屬性作為匯入作業的一部分
    oid:  owner_id,如果指定oid=Y(默認)，則在匯入程序中將分配一個新的oid給物件表，這個引數我們基本不用管，
    pctspace:通過提供一個正數作為該轉換的值，可以增加物件的分配尺寸，并且資料檔案尺寸等于pctspace的值(按百分比)
示例
    transform=segment_attributes:n --表示將用戶所有物件創建到用戶默認表空間，而不再考慮原來的存盤屬性，

• VERSION

此引數主要在跨版本之間進行導資料時使用，更具體一點，是在從高版本資料庫匯入到低版本資料庫時使用,從低版本匯入到高版本，這個引數是不可用的，默認值是:compatible，此引數基本在匯出時使用，匯入時基本不可用，
VERSION={COMPATIBLE | LATEST | version_string}
COMPATIBLE       ： 以引數compatible的值為準，可以通過show parameter 查看compatible引數的值
LATEST           ： 以資料庫版本為準
version_string   ： 指定版本，如： version=10.2.0.1

• SAMPLE

 SAMPLE 給出匯出表資料的百分比，引數值可以取.000001~100（不包括100），不過匯出程序不會和這里給出的百分比一樣精確，是一個近似值， 
 格式： SAMPLE=[[schema_name.]table_name:]sample_percent 
 示例： SAMPLE="HR"."EMPLOYEES":50

• table_exists_action

此引數只在匯入時使用，
作用：匯入時，假如目標庫中已存在對應的表，對于這種情況，提供三種不同的處理方式：append,truncate,skip,replace
格式： table_exists_action=［append | replace| skip |truncate］
說明： append :   追加資料到表中
       truncate:  將目標庫中的同名表的資料truncate掉，
       skip ：      遇到同名表，則跳過，不進行處理，注意：使用此引數值時，與該表相關的所有操作都會skip掉，
       replace:    匯入程序中，遇到同名表，則替換到目標庫的那張表（先drop,再創建），
示例：  table_exists_action=replace

• SQLFILE

只在匯入時使用！
作用： 使用此引數時，主要是將DMP檔案中的metadata陳述句取出到一個單獨的SQLfile中，而資料并不匯入到資料庫中
格式： sqlfile=&file_name.sql
示例： impdp \'\/ as sysdba\' directory=dump_dir dumpfile=halberd.dmp logfile=halberd.log sqlfile=halberd.sql

legacy mode 

在11g中，才有這種模式，這種模式里兼容了以前版本中的部分引數，如：consistent,reuse_dumpfiles等（其實我現在也就知道這兩個引數，哈哈，以后再遇到再補充）

• consistent

這個是保持資料一致性的一個引數，在11g中使用時，如果設定 consistent=true,則會默認轉換成 flashback_time引數，時間設定為命令開始執行的那個時間點，
格式： consistent=[true|false]

• reuse_dumpfiles

作用：重用匯出的dmp檔案 ，假如第一次我們導失敗了，雖然匯出失敗，但是dmp檔案 還 是會生成的，在修改匯出命令，第二次執行時，就可以 加上這個引數，
格式： reuse_dumpfile=[true|false]

• partition_options

1 NONE 不對磁區做特殊處理，在系統上的磁區表一樣創建，
2 DEPARTITION 每個磁區表和子磁區表作為一個獨立的表創建，名字使用表和磁區（子磁區）名字的組合，
3 MERGE 將所有磁區合并到一個表 
注意：如果匯出時使用了TRANSPORTABLE引數，這里就不能使用NONE和MERGE

六、使用技巧

1、不生成檔案直接匯入目標資料庫

在一些情況下，我們并沒有足夠的存盤空間允許我們存盤匯出的dmp檔案，這個時候，我們就無計可施了么？ 不是的，我們可以不生成dmp檔案，直接將資料抽取到目標資料，在遷移大量資料而沒有充足存盤空間時，這是一個救命稻草， 最關鍵的點就是在目標端執行impdp的時候，使用network_link，直接從源庫抽取資料， 示例如下：

cat test.par
directory=dump_dir
logfile=test.log
schemas=test
query="where create_date > last_day(add_months(sysdate,-1)) and create_date <= last_day(sysdate)"
transform=segment_attributes:n
network_link=to_aibcrm
table_exists_action=append
impdp \'\/ as sysdba\' parfile=test.par/

2、通過shell腳本自動匯入

此處只關注，impdp 命令在shell腳本中執行，需要轉義的地方，

cat import_sr.sh
#!/bin/sh
cd /u01/app
for da in 2012-10 2013-09 2013-08 2013-07 2013-06 2013-05 2013-04 2013-03 2013-02 2013-01 2012-12 2012-11 2014-08 2014-07 2014-06 2014-05 2014-04 2014-03 2014-02 2014-01 2013-12 2013-11 2013-10 2015-07 2015-06 2015-05 2015-04 2015-03 2015-02 2015-01 2014-12 2014-11 2014-10 2014-09 2016-06 2016-05 2016-04 2016-03 2016-02 2016-01 2015-12 2015-11 2015-10 2015-09 2015-08 2017-05 2017-04 2017-03 2017-02 2017-01 2016-12 2016-11 2016-10 2016-09 2016-08 2016-07;
do
impdp \'\/ as sysdba\' parfile=import_sr.par logfile=sr${da}.log query=\" where create_date\> last_day\(add_months\(to_date\(\'$da\',\'yyyy-mm\'\),-1\)\) and create_date \<\=last_day\(to_date\(\'$da\',\'yyyy-mm\'\)\)\"
done

-- 引數檔案內容
directory=dump_dir
tables=SR.SR_VOUCHER_FILE_tomig
remap_table=sr.SR_VOUCHER_FILE_tomig:sr_his.sr_voucher_file
transform=segment_attributes:n
network_link=to_aibcrm
table_exists_action=append

3、如何匯出數百張表

include=table:"in (select * from &table_name where_clause)" &table_name :+: 在表里存盤需要匯出的表明細

七、參考地址

文中部分內容出自以下地址，特此注明出處，

http://blog.itpub.net/29785807/viewspace-1593229/

https://www.cnblogs.com/halberd-lee/p/7807032.html

https://www.cnblogs.com/linbo3168/p/6052054.html

https://blog.csdn.net/zhongguomao/article/details/78933333

標籤：其他

上一篇：使用binlog2sql恢復資料

下一篇：資料庫 之 資料庫分類