[原創(chuàng)]Oracle數(shù)據(jù)導(dǎo)入導(dǎo)出(另exp和expdp對比).doc

本文包含exp/imp,expdp/impdp的使用說明和常用參數(shù)詳解另外包括一個(gè)有趣的測試一、Oracle數(shù)據(jù)庫EXPIMPEXPDPIMPDP使用說明1Exp數(shù)據(jù)導(dǎo)出1.1exp關(guān)鍵字說明關(guān)鍵字 說明 (默認(rèn)值) -USERID 用戶名/口令 BUFFER 數(shù)據(jù)緩沖區(qū)大小 FILE 輸出文件 (EXPDAT.DMP) COMPRESS 導(dǎo)入到一個(gè)區(qū) (Y) GRANTS 導(dǎo)出權(quán)限 (Y) INDEXES 導(dǎo)出索引 (Y) DIRECT 直接路徑 (N) -直接導(dǎo)出速度較快 LOG 屏幕輸出的日志文件 ROWS 導(dǎo)出數(shù)據(jù)行 (Y) CONSISTENT 交叉表的一致性 (N) FULL 導(dǎo)出整個(gè)文件 (N)OWNER 所有者用戶名列表TABLES 表名列表RECORDLENGTH IO記錄的長度INCTYPE 增量導(dǎo)出類型RECORD 跟蹤增量導(dǎo)出 (Y)TRIGGERS 導(dǎo)出觸發(fā)器 (Y)STATISTICS 分析對象 (ESTIMATE)PARFILE 參數(shù)文件名CONSTRAINTS 導(dǎo)出的約束條件 (Y)OBJECT_CONSISTENT 只在對象導(dǎo)出期間設(shè)置為只讀的事務(wù)處理 (N)FEEDBACK 每 x 行顯示進(jìn)度 (0)FILESIZE 每個(gè)轉(zhuǎn)儲文件的最大大小FLASHBACK_SCN 用于將會話快照設(shè)置回以前狀態(tài)的 SCNFLASHBACK_TIME 用于獲取最接近指定時(shí)間的 SCN 的時(shí)間QUERY 用于導(dǎo)出表的子集的 select 子句RESUMABLE 遇到與空格相關(guān)的錯誤時(shí)掛起 (N)RESUMABLE_NAME 用于標(biāo)識可恢復(fù)語句的文本字符串RESUMABLE_TIMEOUT RESUMABLE 的等待時(shí)間TTS_FULL_CHECK 對 TTS 執(zhí)行完整或部分相關(guān)性檢查TABLESPACES 要導(dǎo)出的表空間列表TRANSPORT_TABLESPACE 導(dǎo)出可傳輸?shù)谋砜臻g元數(shù)據(jù) (N)TEMPLATE 調(diào)用 iAS 模式導(dǎo)出的模板名1.2常用的exp關(guān)鍵字舉例1、full用于導(dǎo)出整個(gè)數(shù)據(jù)庫，在rows=n一起使用，導(dǎo)出整個(gè)數(shù)據(jù)庫的結(jié)構(gòu)。如：exp userid=gwm/gwm file=/test.dmp log=test.log full=y rows=n direct=y2、OWNER和TABLES，用于定義exp導(dǎo)出的對象如：exp userid=gwm/gwm file=/test.dmp log=test.log owner=gwm table=(table1,table2)3、buffer和feedback 若導(dǎo)出數(shù)據(jù)較大，考慮使用這兩個(gè)參數(shù)。如：exp userid=gwm/gwm file=/test.dmp log=test.log feedback=10000 buffer=100000000 tables=(table1,table2)4、file和log 用于指定備份的dmp名稱和log名稱5、compress 不壓縮導(dǎo)出數(shù)據(jù)的內(nèi)容，默認(rèn)y6、filesize 若導(dǎo)出的數(shù)據(jù)文件大，應(yīng)該用該參數(shù)，限制文件大小不要超過2g如：exp userid=gwm/gwm file=/test1,test2,test3,test4,test5 filesize=2G log=test.log 這樣將創(chuàng)建test1.dmp,test2.dmp等，每個(gè)文件大小為2g。1.3exp導(dǎo)出實(shí)例-數(shù)據(jù)庫全備-exp system/mangerlink file=E:file_name.dmp full=y direct=y log=E:log_name.log-可選擇的備份數(shù)據(jù)庫一些用戶-exp system/mangerlink file=E:file_name.dmp owner=(username1,username2) direct=y log=E:log_name.log-備份一個(gè)用戶的數(shù)據(jù)-exp username/passwordlink file=E:file_name.dmp direct=y log=E:log_name.log2Imp數(shù)據(jù)導(dǎo)入imp關(guān)鍵字說明關(guān)鍵字 說明 (默認(rèn)值) 關(guān)鍵字 說明 (默認(rèn)值)-USERID 用戶名/口令 FULL 導(dǎo)入整個(gè)文件 (N)BUFFER 數(shù)據(jù)緩沖區(qū)大小 FROMUSER 所有者用戶名列表FILE 輸入文件 (EXPDAT.DMP) TOUSER 用戶名列表SHOW 只列出文件內(nèi)容 (N) TABLES 表名列表IGNORE 忽略創(chuàng)建錯誤 (N) RECORDLENGTH IO 記錄的長度GRANTS 導(dǎo)入權(quán)限 (Y) INCTYPE 增量導(dǎo)入類型INDEXES 導(dǎo)入索引 (Y) COMMIT 提交數(shù)組插入 (N)ROWS 導(dǎo)入數(shù)據(jù)行 (Y) PARFILE 參數(shù)文件名LOG 屏幕輸出的日志文件 CONSTRAINTS 導(dǎo)入限制 (Y)DESTROY 覆蓋表空間數(shù)據(jù)文件 (N)INDEXFILE 將表/索引信息寫入指定的文件SKIP_UNUSABLE_INDEXES 跳過不可用索引的維護(hù) (N)FEEDBACK 每 x 行顯示進(jìn)度 (0)TOID_NOVALIDATE 跳過指定類型 ID 的驗(yàn)證FILESIZE 每個(gè)轉(zhuǎn)儲文件的最大大小STATISTICS 始終導(dǎo)入預(yù)計(jì)算的統(tǒng)計(jì)信息RESUMABLE 在遇到有關(guān)空間的錯誤時(shí)掛起 (N)RESUMABLE_NAME 用來標(biāo)識可恢復(fù)語句的文本字符串RESUMABLE_TIMEOUT RESUMABLE 的等待時(shí)間COMPILE 編譯過程, 程序包和函數(shù) (Y)STREAMS_CONFIGURATION 導(dǎo)入流的一般元數(shù)據(jù) (Y)STREAMS_INSTANTIATION 導(dǎo)入流實(shí)例化元數(shù)據(jù) (N)下列關(guān)鍵字僅用于可傳輸?shù)谋砜臻gTRANSPORT_TABLESPACE 導(dǎo)入可傳輸?shù)谋砜臻g元數(shù)據(jù) (N)TABLESPACES 將要傳輸?shù)綌?shù)據(jù)庫的表空間DATAFILES 將要傳輸?shù)綌?shù)據(jù)庫的數(shù)據(jù)文件TTS_OWNERS 擁有可傳輸表空間集中數(shù)據(jù)的用戶3Expdp數(shù)據(jù)泵導(dǎo)出3.1EXPDP命令行選項(xiàng)1. ATTACH該選項(xiàng)用于在客戶會話與已存在導(dǎo)出作用之間建立關(guān)聯(lián).語法如下ATTACH=schema_name.job_nameSchema_name用于指定方案名,job_name用于指定導(dǎo)出作業(yè)名.注意,如果使用ATTACH選項(xiàng),在命令行除了連接字符串和ATTACH選項(xiàng)外,不能指定任何其他選項(xiàng),示例如下:Expdp scott/tiger ATTACH=scott.export_job2. CONTENT該選項(xiàng)用于指定要導(dǎo)出的內(nèi)容.默認(rèn)值為ALLCONTENT=ALL | DATA_ONLY | METADATA_ONLY當(dāng)設(shè)置CONTENT為ALL時(shí),將導(dǎo)出對象定義及其所有數(shù)據(jù).為DATA_ONLY時(shí),只導(dǎo)出對象數(shù)據(jù),為METADATA_ONLY時(shí),只導(dǎo)出對象定義Expdp scott/tiger DIRECTORY=dump DUMPFILE=a.dump CONTENT=METADATA_ONLY3. DIRECTORY指定轉(zhuǎn)儲文件和日志文件所在的目錄DIRECTORY=directory_objectDirectory_object用于指定目錄對象名稱.需要注意,目錄對象是使用CREATE DIRECTORY語句建立的對象,而不是OS目錄Expdp scott/tiger DIRECTORY=dump DUMPFILE=a.dump4. DUMPFILE用于指定轉(zhuǎn)儲文件的名稱,默認(rèn)名稱為expdat.dmpDUMPFILE=directory_object:file_name ,.Directory_object用于指定目錄對象名,file_name用于指定轉(zhuǎn)儲文件名.需要注意,如果不指定directory_object,導(dǎo)出工具會自動使用DIRECTORY選項(xiàng)指定的目錄對象Expdp scott/tiger DIRECTORY=dump1 DUMPFILE=dump2:a.dmp5. ESTIMATE指定估算被導(dǎo)出表所占用磁盤空間分方法.默認(rèn)值是BLOCKSESTIMATE=BLOCKS | STATISTICS設(shè)置為BLOCKS時(shí),oracle會按照目標(biāo)對象所占用的數(shù)據(jù)塊個(gè)數(shù)乘以數(shù)據(jù)塊尺寸估算對象占用的空間,設(shè)置為STATISTICS時(shí),根據(jù)最近統(tǒng)計(jì)值估算對象占用空間Expdp scott/tiger TABLES=emp ESTIMATE=STATISTICS DIRECTORY=dump DUMPFILE=a.dump6. ESTIMATE_ONLY指定是否只估算導(dǎo)出作業(yè)所占用的磁盤空間,默認(rèn)值為NEXTIMATE_ONLY=Y | N設(shè)置為Y時(shí),導(dǎo)出作用只估算對象所占用的磁盤空間,而不會執(zhí)行導(dǎo)出作業(yè),為N時(shí),不僅估算對象所占用的磁盤空間,還會執(zhí)行導(dǎo)出操作.Expdp scott/tiger ESTIMATE_ONLY=y NOLOGFILE=y7. EXCLUDE(具體見2、Exclude導(dǎo)出用戶中指定類型的指定對象)該選項(xiàng)用于指定執(zhí)行操作時(shí)釋放要排除對象類型或相關(guān)對象EXCLUDE=object_type:name_clause ,.Object_type用于指定要排除的對象類型,name_clause用于指定要排除的具體對象.EXCLUDE和INCLUDE不能同時(shí)使用Expdp scott/tiger DIRECTORY=dump DUMPFILE=a.dup EXCLUDE=VIEW8. FILESIZE指定導(dǎo)出文件的最大尺寸,默認(rèn)為0,(表示文件尺寸沒有限制)9. FLASHBACK_SCN指定導(dǎo)出特定SCN時(shí)刻的表數(shù)據(jù)FLASHBACK_SCN=scn_valueScn_value用于標(biāo)識SCN值.FLASHBACK_SCN和FLASHBACK_TIME不能同時(shí)使用Expdp scott/tiger DIRECTORY=dump DUMPFILE=a.dmp FLASHBACK_SCN=35852310. FLASHBACK_TIME指定導(dǎo)出特定時(shí)間點(diǎn)的表數(shù)據(jù)FLASHBACK_TIME=”TO_TIMESTAMP(time_value)”Expdp scott/tiger DIRECTORY=dump DUMPFILE=a.dmp FLASHBACK_TIME=“TO_TIMESTAMP(25-08-2004 14:35:00,DD-MM-YYYY HH24:MI:SS)”11. FULL指定數(shù)據(jù)庫模式導(dǎo)出,默認(rèn)為NFULL=Y | N為Y時(shí),標(biāo)識執(zhí)行數(shù)據(jù)庫導(dǎo)出.12. HELP指定是否顯示EXPDP命令行選項(xiàng)的幫助信息,默認(rèn)為N當(dāng)設(shè)置為Y時(shí),會顯示導(dǎo)出選項(xiàng)的幫助信息.Expdp help=y13. INCLUDE（具體見1、Include導(dǎo)出用戶中指定類型的指定對象）指定導(dǎo)出時(shí)要包含的對象類型及相關(guān)對象INCLUDE = object_type:name_clause , 14. JOB_NAME指定要導(dǎo)出作用的名稱,默認(rèn)為SYS_XXXJOB_NAME=jobname_stringSELECT * FROM DBA_DATAPUMP_JOBS;-查看存在的job15. LOGFILE指定導(dǎo)出日志文件文件的名稱,默認(rèn)名稱為export.logLOGFILE=directory_object:file_nameDirectory_object用于指定目錄對象名稱,file_name用于指定導(dǎo)出日志文件名.如果不指定directory_object.導(dǎo)出作用會自動使用DIRECTORY的相應(yīng)選項(xiàng)值.Expdp scott/tiger DIRECTORY=dump DUMPFILE=a.dmp logfile=a.log16. NETWORK_LINK指定數(shù)據(jù)庫鏈名,如果要將遠(yuǎn)程數(shù)據(jù)庫對象導(dǎo)出到本地例程的轉(zhuǎn)儲文件中,必須設(shè)置該選項(xiàng).如：expdp gwm/gwm directory=dir_dp NETWORK_LINK=igisdb tables=p_street_area dumpfile =p_street_area.dmp logfile=p_street_area.log job_name=my_jobigisdb是目的數(shù)據(jù)庫與源數(shù)據(jù)的鏈接名，dir_dp是目的數(shù)據(jù)庫上的目錄而如果直接用使用連接字符串(fgisdb),expdp屬于服務(wù)端工具，expdp生成的文件默認(rèn)是存放在服務(wù)端的17. NOLOGFILE該選項(xiàng)用于指定禁止生成導(dǎo)出日志文件,默認(rèn)值為N.18. PARALLEL指定執(zhí)行導(dǎo)出操作的并行進(jìn)程個(gè)數(shù),默認(rèn)值為1注：并行度設(shè)置不應(yīng)該超過CPU數(shù)的2倍，如果cpu為2個(gè)，可將PARALLEL設(shè)為2，在導(dǎo)入時(shí)速度比PARALLEL為1要快而對于導(dǎo)出的文件，如果PARALLEL設(shè)為2，導(dǎo)出文件只有一個(gè)，導(dǎo)出速度提高不多，因?yàn)閷?dǎo)出都是到同一個(gè)文件，會爭搶資源。所以可以設(shè)置導(dǎo)出文件為兩個(gè),如下所示：expdp gwm/gwm directory=d_test dumpfile=gwmfile1.dp,gwmfile2.dp parallel=219. PARFILE指定導(dǎo)出參數(shù)文件的名稱PARFILE=directory_path file_name20. QUERY用于指定過濾導(dǎo)出數(shù)據(jù)的where條件QUERY=schema. table_name: query_clauseSchema用于指定方案名,table_name用于指定表名,query_clause用于指定條件限制子句.QUERY選項(xiàng)不能與CONNECT=METADATA_ONLY,EXTIMATE_ONLY,TRANSPORT_TABLESPACES等選項(xiàng)同時(shí)使用.Expdp scott/tiger directory=dump dumpfile=a.dmp Tables=emp query=WHERE deptno=2021. SCHEMAS該方案用于指定執(zhí)行方案模式導(dǎo)出,默認(rèn)為當(dāng)前用戶方案.22. STATUS指定顯示導(dǎo)出作用進(jìn)程的詳細(xì)狀態(tài),默認(rèn)值為023. TABLES指定表模式導(dǎo)出TABLES=schema_name.table_name:partition_name,Schema_name用于指定方案名,table_name用于指定導(dǎo)出的表名,partition_name用于指定要導(dǎo)出的分區(qū)名.24. TABLESPACES指定要導(dǎo)出表空間列表25. TRANSPORT_FULL_CHECK該選項(xiàng)用于指定被搬移表空間和未搬移表空間關(guān)聯(lián)關(guān)系的檢查方式,默認(rèn)為N.當(dāng)設(shè)置為Y時(shí),導(dǎo)出作用會檢查表空間直接的完整關(guān)聯(lián)關(guān)系,如果表空間所在表空間或其索引所在的表空間只有一個(gè)表空間被搬移,將顯示錯誤信息.當(dāng)設(shè)置為N時(shí),導(dǎo)出作用只檢查單端依賴,如果搬移索引所在表空間,但未搬移表所在表空間,將顯示出錯信息,如果搬移表所在表空間,未搬移索引所在表空間,則不會顯示錯誤信息.26. TRANSPORT_TABLESPACES指定執(zhí)行表空間模式導(dǎo)出27. VERSION指定被導(dǎo)出對象的數(shù)據(jù)庫版本,默認(rèn)值為COMPATIBLE.VERSION=COMPATIBLE | LATEST | version_string為COMPATIBLE時(shí),會根據(jù)初始化參數(shù)COMPATIBLE生成對象元數(shù)據(jù);為LATEST時(shí),會根據(jù)數(shù)據(jù)庫的實(shí)際版本生成對象元數(shù)據(jù).version_string用于指定數(shù)據(jù)庫版本字符串.調(diào)用EXPDP3.2數(shù)據(jù)泵工具導(dǎo)出的步驟1、創(chuàng)建DIRECTORYcreate directory dir_dp as D:oracledir_dp; 2、授權(quán)Grant read,write on directory dir_dp to lttfm;-查看目錄及權(quán)限SELECT privilege, directory_name, DIRECTORY_PATH FROM user_tab_privs t, all_directories dWHERE t.table_name(+) = d.directory_name ORDER BY 2, 1;3、執(zhí)行導(dǎo)出expdp lttfm/lttfmfgisdb schemas=lttfm directory=dir_dp dumpfile =expdp_test1.dmp logfile=expdp_test1.log;連接到: Oracle Database 10g Enterprise Edition Release 10.2.0.1With the Partitioning, OLAP and Data Mining options啟動 LTTFM.SYS_EXPORT_SCHEMA_01: lttfm/*fgisdb schory=dir_dp dumpfile =expdp_test1.dmp logfile=expdp_test1.log; */3.3其他使用說明1、directory=dir_dp必須放在前面，如果將其放置最后，會提示 ORA-39002: 操作無效ORA-39070: 無法打開日志文件。ORA-39087: 目錄名 DATA_PUMP_DIR; 無效2、在導(dǎo)出過程中，DATA DUMP 創(chuàng)建并使用了一個(gè)名為SYS_EXPORT_SCHEMA_01的對象，此對象就是DATA DUMP導(dǎo)出過程中所用的JOB名字，如果在執(zhí)行這個(gè)命令時(shí)如果沒有指定導(dǎo)出的JOB名字那么就會產(chǎn)生一個(gè)默認(rèn)的JOB名字，如果在導(dǎo)出過程中指定JOB名字就為以指定名字出現(xiàn)如下改成：expdp lttfm/lttfmfgisdb schemas=lttfm directory=dir_dp dumpfile =expdp_test1.dmp logfile=expdp_test1.log,job_name=my_job1;3、導(dǎo)出語句后面不要有分號，否則如上的導(dǎo)出語句中的job表名為my_job1;，而不是my_job1。因此導(dǎo)致expdp lttfm/lttfm attach=lttfm.my_job1執(zhí)行該命令時(shí)一直提示找不到j(luò)ob表4、創(chuàng)建的目錄一定要在數(shù)據(jù)庫所在的機(jī)器上。否則也是提示:ORA-39002: 操作無效ORA-39070: 無法打開日志文件。ORA-39087: 目錄名 DATA_PUMP_DIR; 無效3.4導(dǎo)出的其他相關(guān)命令使用1）Ctrl+C組合鍵：在執(zhí)行過程中，可以按Ctrl+C組合鍵退出當(dāng)前交互模式，退出之后，導(dǎo)出操作不會停止2）Export status -查看當(dāng)前JOB的狀態(tài)及相關(guān)信息3）Export stop_job -暫停JOB（暫停job后會退出expor模式）4）重新進(jìn)入export模式下：C:Documents and SettingsAdministratorexpdp lttfm/lttfm attach=lttfm.my_job1 -語句后面不帶分號5）Export start_job -打開暫停的JOB（并未開始重新執(zhí)行）6）Export continue_client -通過此命令重新啟動 LTTFM.MY_JOB:7）Export kill_job -取消當(dāng)前的JOB并釋放相關(guān)客戶會話（將job刪除同時(shí)刪除dmp文件）8）Export exit_client -通過此命令退出export模式（通過4）可再進(jìn)入export模式下）注：導(dǎo)出完成后job自動卸載3.5數(shù)據(jù)泵導(dǎo)出的各種模式1、按表模式導(dǎo)出：expdp lttfm/lttfmfgisdb tables=lttfm.b$i_exch_info,lttfm.b$i_manhole_info dumpfile =expdp_test2.dmp logfile=expdp_test2.log directory=dir_dp job_name=my_job2、按查詢條件導(dǎo)出：expdp lttfm/lttfmfgisdb tables=lttfm.b$i_exch_info dumpfile =expdp_test3.dmp logfile=expdp_test3.log directory=dir_dp job_name=my_job query=where rownumprompt $P$G$T$GC:Documents and SettingsAdministrator16:10:55.37C:Documents and SettingsAdministrator16:11:05.92exp user_exp/user_exporcl_65 file=d:exp_01.dmp log=d:exp_01.log tables=(t_exp)Export: Release 10.2.0.1.0 - Production on 星期三 10月 12 16:11:07 2011Copyright (c) 1982, 2005, Oracle. All rights reserved.連接到: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - ProductionWith the Partitioning, OLAP and Data Mining options已導(dǎo)出 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集服務(wù)器使用 AL32UTF8 字符集 (可能的字符集轉(zhuǎn)換)即將導(dǎo)出指定的表通過常規(guī)路徑. . 正在導(dǎo)出表 T_EXP導(dǎo)出了 10000007 行成功終止導(dǎo)出, 沒有出現(xiàn)警告。C:Documents and SettingsAdministrator16:17:51.04用時(shí)：6分鐘46秒Expdp導(dǎo)出過程如下：C:Documents and SettingsAdministrator16:22:15.25expdp db_exp/db_exporcl_67directory=DIR_EXP dumpfile=expdp_01.dmp logfile=expdp_01.log tables=(t_exp) network_link=to_exp_65Export: Release 10.2.0.1.0 - Production on 星期三, 12 10月, 2011 16:22:16Copyright (c) 2003, 2005, Oracle. All rights reserved.連接到: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - ProductionWith the Partitioning, OLAP and Data Mining options啟動 DB_EXP.SYS_EXPORT_TABLE_01: db_exp/*orcl_67 directory=DIR_EXPdumpfile=expdp_01.dmp logfile=expdp_01.log tables=(t_exp) network_link=to_exp_65正在使用 BLOCKS 方法進(jìn)行估計(jì).處理對象類型 TABLE_EXPORT/TABLE/TABLE_DATA使用 BLOCKS 方法的總估計(jì): 336 MB處理對象類型 TABLE_EXPORT/TABLE/TABLE. . 導(dǎo)出了 USER_EXP.T_EXP 285.8 MB 10000007 行已成功加載/卸載了主表 DB_EXP.SYS_EXPORT_TABLE_01*DB_EXP.SYS_EXPORT_TABLE_01 的轉(zhuǎn)儲文件集為: D:DIREXPEXPDP_01.DMP作業(yè) DB_EXP.SYS_EXPORT_TABLE_01 已于 16:23:35 成功完成C:Documents and SettingsAdministrator16:23:36.46用時(shí)：1分鐘21秒另：Expdp導(dǎo)入到遠(yuǎn)程數(shù)據(jù)庫服務(wù)端 所耗時(shí)間如下：C:Documents and SettingsAdministrator16:27:40.92expdp user_exp/us