《expimp 与 expdpimpdp 对比 及使用中的一些优化事项》由会员分享，可在线阅读，更多相关《expimp 与 expdpimpdp 对比 及使用中的一些优化事项（12页珍藏版）》请在金锄头文库上搜索。
1、关于exp/imp和expdp/impdp, 之前整理的2篇Blog如下:ORACLE数据库逻辑备份简单EXP/IMPhttp:/ 10g EXPDP 和 IMPDP 使用说明http:/ 与 expdp/impdp 对比1.1 expdp/impdp调用Server端的API在执行操作，是数据库内部的job任务。可以远程使用, 但是生成的dump文件存在于服务器上的directory里。1.2 exp/imp与expdp/impdp的默认模式和原理不一样1.2.1 exp/imp不同模式原理在metalink的这边文章中，提到了 exp/imp的不同模式下的工作原理：Parameter DI
2、RECT: Conventional Path Export Versus Direct Path Export ID 155477.1http:/ with Oracle7 release 7.3, the Export utility provides two methods for exporting table data: -Conventional Path Export-Direct Path Export(1) Conventional path Export.Conventional path Export uses the SQL SELECT statement to ex
3、tract data from tables. Data is read from disk into the buffer cache, and rows are transferred to the evaluating buffer. The data, after passing expression evaluation, is transferred to the Export client, which then writes the data into the export file.exp/imp默认会是传统路径，这种模式下，是用SELECT加数据查询出来， 然后写入buff
4、er cache，在将这些记录写入evaluate buffer.最后传到Export客户端，在写入 dump文件。(2) Direct path Export.When using a Direct path Export, the data is read from disk directly into the export sessions program global area (PGA): the rows are transferred directly to the Export sessions private buffer. This also means that the
5、SQL command-processing layer (evaluation buffer) can be bypassed, because the data is already in the format that Export expects. As a result, unnecessary data conversion is avoided. The data is transferred to the Export client, which then writes the data into the export file.The default is DIRECT=N,
6、 which extracts the table data using the conventional path.This parameter is only applicable to the original export client. Export DataPump (expdp) uses a Direct Path unload by default and switches to External Table mode if required直接路径模式下，数据直接从硬盘读取，然后写入PGA，格式就是export的 格式，不需要转换，数据再直接传到export客户端，写入du
7、mp文件。这种模式没有经过evaluation buffer。少了一个过程，导出速度提高也是很明显。1.2.2 expdp/impdp 不同模式Export/Import DataPump Parameter ACCESS_METHOD - How to Enforce aMethod of Loading and Unloading Data ? ID 552424.1http:/ two most commonly used methods to move data in and out of databases with Data Pump are the Direct Path met
8、hod and the External Tables method.（1）Direct Path mode.After data file copying, direct path is the fastest method of moving data. In this method, the SQL layer of the database is bypassed and rows are moved to and from the dump file with only minimal interpretation. Data Pump automatically uses the
9、direct path method for loading and unloading data when the structure of a table allows it.expdp/impdp默认就是使用直接路径的，所以expdp要比exp块。（2）External Tables mode.If data cannot be moved in direct path mode, or if there is a situation where parallel SQL can be used to speed up the data move even more, then the
10、external tables mode is used. The external table mechanism creates an external table that maps the dump file data for the database table. The SQL engine is then used to move the data. If possible, the APPEND hint is used on import to speed the copying of the data into the database.Note: When the Exp
11、ort NETWORK_LINK parameter is used to specify a network link for an export operation, a variant of the external tables method is used. In this case, data is selected from across the specified network link and inserted into the dump file using an external table.(3) Data File Copying mode.This mode is
12、 used when a transport tablespace job is started, i.e.: the TRANSPORT_TABLESPACES parameter is specified for an Export Data Pump job. This is the fastest method of moving data because the data is not interpreted nor altered during the job, and Export Data Pump is used to unload only structural infor
13、mation (metadata) into the dump file.(4) Network Link Import mode.This mode is used when the NETWORK_LINK parameter is specified during an Import Data Pump job. This is the slowest of the four access methods because this method makes use of an INSERT SELECT statement to move the data over a database
14、 link, and reading over a network is generally slower than reading from a disk.这种模式很方便，但是速度是最慢的，因为它是通过insert, select + dblink来实现的。速 度慢也由此可见了。示例：create directory dump1 as /oradata/dumpfiles; grant read,write on dump1 to xxx;创建 DBLINK:/* Formatted on 2010/12/23 11:28:22 (QP5 v5.115.810.9015) */CREATE
15、DATABASE LINK TIANLESOFTWARECONNECT TO BUSINESSIDENTIFIED BYvPWDUSING(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = IP ADDRESS)(PORT = 1521)(CONNECT_DATA = (SID = ORCL)(SERVER = DEDICATED)；Dumpfile参数，可以用口指定expdp xxx/xxx schemas=xxx directory=dumpl dumpfile=xxx_%U.dmp filesize=5g这样每个文件 5G , xxx_01.dump,xxx_02.dump 这样。关于U参考：http:/download.oracle.Com/docs/cd/B19306_01/server.102
