【原創】比較資料泵和exp/imp對相同資料匯出/匯入的效能差異
EXP/IMP 是資料庫傳統的匯出匯入工具,它有使用方便,在服務端和客戶端都可用的優點,速度沒有expdp快,功能沒有expdp強大
EXP 工具幫助文件,我們可以參考幫助文件進行命令列配置
[oracle@leonarding1 ~]$ exp –help 或者 exp help=y
Export: Release 11.2.0.1.0 - Production onThu Jun 20 07:28:33 2013
Copyright (c) 1982, 2009, Oracle and/or itsaffiliates. All rights reserved.
You can let Export prompt you forparameters by entering the EXP
command followed by your username/password:
Example: EXP SCOTT/TIGER
Or, you can control how Export runs byentering the EXP command followed
by various arguments. To specifyparameters, you use keywords:
Format: EXP KEYWORD=value rKEYWORD=(value1,value2,...,valueN)
Example: EXP SCOTT/TIGER GRANTS=Y TABLES=(EMP,DEPT,MGR)
or TABLES=(T1:P1,T1:P2), if T1is partitioned table
USERID must be the first parameter on thecommand line.
Keyword Description (Default) Keyword Description (Default)
--------------------------------------------------------------------------
USERID username/password FULL export entire file(N)
BUFFER size of data buffer OWNER list of ownerusernames
FILE output files (EXPDAT.DMP) TABLES list of table names
COMPRESS import into one extent (Y) RECORDLENGTH length of IO record
GRANTS export grants (Y) INCTYPE incremental exporttype
INDEXES export indexes (Y) RECORD track incr. export(Y)
DIRECT direct path (N) TRIGGERS export triggers (Y)
LOG log file of screen output STATISTICS analyze objects(ESTIMATE)
ROWS export data rows (Y) PARFILE parameter filename
CONSISTENT cross-table consistency(N)CONSTRAINTS export constraints (Y)
OBJECT_CONSISTENT transaction set to read only during objectexport (N)
FEEDBACK display progress every x rows (0)
FILESIZE maximum size of each dump file
FLASHBACK_SCN SCN used to set session snapshot backto
FLASHBACK_TIME time used to get the SCN closest to thespecified time
QUERY select clause used to export asubset of a table
RESUMABLE suspend when a space related erroris encountered(N)
RESUMABLE_NAME text string used to identify resumablestatement
RESUMABLE_TIMEOUT wait time for RESUMABLE
TTS_FULL_CHECK perform. full or partial dependency checkfor TTS
VOLSIZE number of bytes to write to eachtape volume
TABLESPACES list of tablespaces to export
TRANSPORT_TABLESPACE export transportabletablespace metadata (N)
TEMPLATE template name which invokes iASmode export
Export terminated successfully withoutwarnings.
#########################################################################
建立測試表leo1
LEO1@LEO1>set linesize 400 pagesize 999 格式化
LEO1@LEO1>drop table leo1 purge; 清空環境
Table dropped.
LEO1@LEO1>create table leo1 as select *from dba_objects; 建立測試表leo1
Table created.
LEO1@LEO1>insert into leo1 select * fromleo1;
72543 rows created.
LEO1@LEO1>insert into leo1 select * fromleo1;
145086 rows created.
LEO1@LEO1>insert into leo1 select * fromleo1;
290172 rows created.
LEO1@LEO1>insert into leo1 select * fromleo1;
580344 rows created.
LEO1@LEO1>commit;
Commit complete.
LEO1@LEO1>insert into leo1 select * fromleo1;
1160688 rows created.
LEO1@LEO1>commit;
Commit complete.
LEO1@LEO1>select count(*) from leo1; 現在是232萬
COUNT(*)
----------
2321376
[oracle@leonarding1 ~]$ mkdir exp_dump 建立一個匯出檔案的資料夾
[oracle@leonarding1 ~]$ exp leo1/leo1file='/home/oracle/exp_dump/leo1.dmp' tables=leo1 rows=y
Export: Release11.2.0.1.0 - Production on Fri Jun 21 06:38:28 2013
Copyright (c) 1982, 2009, Oracle and/or itsaffiliates. All rights reserved.
Connected to: Oracle Database 11gEnterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Miningand Real Application Testing options
Export done in US7ASCII character set andAL16UTF16 NCHAR character set
server uses ZHS16GBK character set(possible charset conversion)
About to export specified tables viaConventional Path ...
. . exporting table LEO1 2321376 rows exported
Export terminated successfully withoutwarnings. 成功終止匯出,沒有出現告警
[oracle@leonarding1 ~]$ date
Fri Jun 21 06:39:40 CST 2013
匯出從06:38:28開始到06:39:40結束,一共用時72秒
LEO1@LEO1>truncate table leo1; 清空表,我再匯入一下
Table truncated.
[oracle@leonarding1 ~]$ cd exp_dump/
[oracle@leonarding1 exp_dump]$ ll
total 261356
-rw-r--r-- 1 oracle oinstall 267362304 Jun21 06:39 leo1.dmp 這個就是我們匯出的檔案
[oracle@leonarding1 exp_dump]$ impleo1/leo1 file='/home/oracle/exp_dump/leo1.dmp' full=y ignore=y
Import: Release11.2.0.1.0 - Production on Fri Jun 21 06:45:04 2013
Copyright (c) 1982, 2009, Oracle and/or itsaffiliates. All rights reserved.
Connected to: Oracle Database 11gEnterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Miningand Real Application Testing options
Export file created by EXPORT:V11.02.00 viaconventional path
import done in US7ASCII character set andAL16UTF16 NCHAR character set
import server uses ZHS16GBK character set(possible charset conversion)
. importing LEO1's objects into LEO1
. importing LEO1's objects into LEO1
. . importing table "LEO1" 2321376 rows imported
Import terminated successfully withoutwarnings. 成功終止匯入,沒有出現告警
[oracle@leonarding1 exp_dump]$ date
Fri Jun 21 06:48:26 CST 2013
匯入從06:45:04開始到06:48:26結束,一共用時202秒
EXPDP/IMPDP 是Oracle推崇的資料泵匯入匯出工具,用於代替傳統的EXP/IMP,只能在服務端使用,效率比EXP/IMP快幾十倍,有續傳功能和並行功能。這個工具始於Oracle10g,從Oracle11g開始不再提供老的EXP/IMP的諮詢但工具還可以使用。
EXPDP工具的效率差不多比EXP快幾倍
IMPDP工具的效率差不多比IMP快幾十倍
所以說這兩個工具適用於大資料匯入匯出的場景
匯出檔案的格式更接近於資料庫本身的檔案格式,避免了資料寫入檔案時的轉換
直接路徑載入,跳過SGA記憶體區,直接載入到高水位線之後
後設資料metadata和資料data在匯出的過程中可以重疊進行,提高匯出的效率。
我們再來看看EXPDP/IMPDP的匯出匯入效果
在使用EXPDP/IMPDP之前我們需要定義一個目錄物件,告知EXPDP/IMPDP工具匯出的檔案和匯入的檔案的存放目錄
LEO1@LEO1>create directory exp_dump as'/home/oracle/exp_dump';
Directory created.
[oracle@leonarding1 exp_dump]$ expdpleo1/leo1 directory=exp_dump dumpfile=expdp_leo1.dmp tables=leo1;
Export: Release11.2.0.1.0 - Production on Fri Jun 21 07:07:09 2013
Copyright (c) 1982, 2009, Oracle and/or itsaffiliates. All rights reserved.
Connected to: Oracle Database 11gEnterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Miningand Real Application Testing options
Starting"LEO1"."SYS_EXPORT_TABLE_01": leo1/******** directory=exp_dumpdumpfile=expdp_leo1.dmp tables=leo1
Estimate in progress using BLOCKS method...
Processing object typeTABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 264MB
Processing object typeTABLE_EXPORT/TABLE/TABLE
. . exported"LEO1"."LEO1" 223.9 MB 2321376 rows
Master table"LEO1"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for LEO1.SYS_EXPORT_TABLE_01is:
/home/oracle/exp_dump/expdp_leo1.dmp
Job"LEO1"."SYS_EXPORT_TABLE_01" successfully completed at07:08:22
匯出從07:07:09開始到07:08:22結束,一共用時73秒,匯出檔案大小223.9MB 行數2321376 rows,與exp時間相差1秒,expdp在資料量比較大的時候會體現出高效率。
現在我們impdp匯入
LEO1@LEO1>drop table leo1; 先把表刪除,因為如果表結構存在的話,impdp會認為後設資料已存在報錯,不可匯入
Table dropped.
[oracle@leonarding1 exp_dump]$ impdpleo1/leo1 directory=exp_dump dumpfile=expdp_leo1.dmp
Import: Release11.2.0.1.0 - Production on Fri Jun 21 07:24:07 2013
Copyright (c) 1982, 2009, Oracle and/or itsaffiliates. All rights reserved.
Connected to: Oracle Database 11gEnterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Miningand Real Application Testing options
Master table"LEO1"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting"LEO1"."SYS_IMPORT_FULL_01": leo1/******** directory=exp_dumpdumpfile=expdp_leo1.dmp
Processing object typeTABLE_EXPORT/TABLE/TABLE
Processing object typeTABLE_EXPORT/TABLE/TABLE_DATA
. . imported"LEO1"."LEO1" 223.9 MB 2321376rows
Job"LEO1"."SYS_IMPORT_FULL_01" successfully completed at07:27:27
匯入從07:24:07開始到07:27:27結束,一共用時200秒,匯入檔案大小223.9MB 行數2321376 rows,比imp時間節約了2秒,impdp也是在資料量比較大的時候會體現出高效率。
2013.6.22
北京&summer
分享~成就夢想
Blog:
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/26686207/viewspace-764595/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 資料匯入匯出EXP/IMP
- exp/imp匯出匯入資料
- oracle資料匯出匯入(exp/imp)Oracle
- oracle資料的匯入匯出imp/expOracle
- 10g資料泵和匯入匯出效能對比(三)
- 10g資料泵和匯入匯出效能對比(二)
- 10g資料泵和匯入匯出效能對比(一)
- 10g資料泵和匯入匯出效能對比(四)
- 10g資料泵和匯入匯出效能對比(六)
- 10g資料泵和匯入匯出效能對比(五)
- Oracle資料匯入匯出imp/exp命令Oracle
- Oracle資料庫匯入匯出。imp匯入命令和exp匯出命令Oracle資料庫
- Oracle資料匯入匯出imp/exp命令(轉)Oracle
- Oracle資料泵的匯入和匯出Oracle
- 資料泵匯出匯入
- 資料泵的匯入匯出
- 【EXP/IMP】使用EXP /IMP工具“模糊”匯出和匯入
- 資料泵取匯出和匯入(一)
- 使用Oracle 的 imp ,exp 命令實現資料的匯入匯出Oracle
- 【原創】MySQL和PostgreSQL 匯入資料對比 薦MySql
- 資料泵匯出匯入表
- 使用Exp和Expdp匯出資料的效能對比與優化優化
- SQL Server資料庫匯入匯出資料方式比較SQLServer資料庫
- 使用exp/imp匯出匯入資料(邏輯備份恢復)
- Oracle中exp,imp(匯入匯出)資料遷移注意事項Oracle
- 使用Exp和Expdp匯出資料的效能對比與最佳化
- 對比資料泵與原始匯入匯出工具(五)
- 對比資料泵與原始匯入匯出工具(四)
- 對比資料泵與原始匯入匯出工具(三)
- 對比資料泵與原始匯入匯出工具(八)
- 對比資料泵與原始匯入匯出工具(七)
- 對比資料泵與原始匯入匯出工具(六)
- Oracle資料泵-schema匯入匯出Oracle
- 資料泵匯出匯入資料標準文件
- Windows DOS窗體下Oracle 資料庫的匯入匯出(IMP/EXP)命令WindowsOracle資料庫
- Oracle資料泵匯出匯入(expdp/impdp)Oracle
- Oracle使用資料泵匯出匯入表Oracle
- shell,ant指令碼實現自動資料泵(exp.expdp)匯出匯入資料指令碼