| EXPDP/IMPDP 参数小结 | DBA之路：OoNiceDream |

EXPDP/IMPDP 参数小结

作者：OoNiceDream【转载时请务必以超链接形式标明文章原始出处和作者信息】
链接：http://www.dbaroad.me/archives/2009/08/expdp_impdp_parameters.html

一直都没好好用过EXPDP/IMPDP工具，找了点资料，做了些实验，发现速度确实比EXP/IMP快了不少，功能也很强大。对其中几个参数，做一点总结：
DIRECTORY
EXPDP/IMPDP工具是基于服务端的，只要任务调起来了，就算客户端退出了EXPDP/IMPDP进程，任务仍会在服务端运行。
所以这个DIRECTORY定义的是一个服务端的路径，存放导出的DUMPFILE，常见的操作如下：

SQL> CREATE OR REPLACE DIRECTORY EXP_DIR as '/oradata/exp/';
 
Directory created.
 
SQL> select * from dba_directories
  2  where directory_name='EXP_DIR';
 
OWNER      DIRECTORY_NAME       DIRECTORY_PATH
---------- -------------------- --------------------
SYS        EXP_DIR              /oradata/exp/
 
SQL> grant read,write on DIRECTORY EXP_DIR to rockey;
 
Grant succeeded.
 
SQL> select * from dba_tab_privs
  2  where table_name='EXP_DIR';
 
GRANTEE    OWNER      TABLE_NAME GRANTOR    PRIVILEGE  GRA HIE
---------- ---------- ---------- ---------- ---------- --- ---
ROCKEY     SYS        EXP_DIR    SYS        WRITE      NO  NO
ROCKEY     SYS        EXP_DIR    SYS        READ       NO  NO
 
SQL> drop directory EXP_DIR;
 
Directory dropped.

JOB_NAME
EXPDP/IMPDP 通过转化为数据库的JOB，可实现了任务的停止和重启。
指定JOB NAME，数据库中会创建一张相应名称的master table（MT），master table是用于记录EXPDP/IMPDP任务的相关信息。如果不指定，数据库将自动命名MT。JOB_NAME的指定，便于EXPDP任务的管理，建议命名格式：有意义名称+时间，例如这里我们将JOB_NAME指定为EXP_TEST_20090815。

Master Table的查询：

SQL> select owner,table_name from dba_tables
  2  where table_name='EXP_TEST_20090815';
 
OWNER                          TABLE_NAME
------------------------------ ------------------------------
ROCKEY                         EXP_TEST_20090815

JOB信息查询：

SQL> select * from dba_datapump_jobs
  2  where job_name='EXP_TEST_20090815';
 
OWNER_NAME JOB_NAME          OPERATION JOB_MODE STATE     DEGREE ATTACHED_SESSIONS DATAPUMP_SESSIONS
---------- ----------------- --------- -------- --------- ------ ----------------- -----------------
ROCKEY     EXP_TEST_20090815 EXPORT    TABLE    EXECUTING      1                 1                 3

ATTACH
EXPDP/IMPDP 的交互模式是相当强大的一点，通过ATTACH JOB，我们可以进入交互模式，进行包括stop、start、kill任务，对于EXPDP/IMPDP任务的管理，十分的方便。
进入交互模式时，首先会显示该任务的状态信息，通过stop_job可以中断任务：

$ expdp rockey/rockey@EMREP attach=exp_test_20090815
 
Export: Release 10.2.0.1.0 - 64bit Production on Saturday, 15 August, 2009 15:04:52
 
Copyright (c) 2003, 2005, Oracle.  All rights reserved.
 
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
 
Job: EXP_TEST_20090815
  Owner: ROCKEY                         
  Operation: EXPORT                         
  Creator Privs: FALSE                          
  GUID: 7128C430B6C038D3E04400163584A0F7
  Start Time: Saturday, 15 August, 2009 15:03:31
  Mode: TABLE                          
  Instance: EMREP
  Max Parallelism: 1
  EXPORT Job Parameters:
  Parameter Name      Parameter Value:
     CLIENT_COMMAND        parfile=/oradata/exp/expdp.par logfile=expdp14540.log 
  State: EXECUTING                      
  Bytes Processed: 0
  Current Parallelism: 1
  Job Error Count: 0
  Dump File: /oradata/exp/exp_test.dmp
    bytes written: 8,192
 
Worker 1 Status:
  State: EXECUTING                      
  Object Schema: ROCKEY
  Object Name: TEST
  Object Type: TABLE_EXPORT/TABLE/TABLE_DATA
  Completed Objects: 1
  Total Objects: 1
  Completed Rows: 13,387,546
  Worker Parallelism: 1
 
Export> stop_job
Are you sure you wish to stop this job ([yes]/no): yes

通过start_job，可以重启任务，不管是手工中断还是意外中断的任务：

$ expdp rockey/rockey@EMREP attach=exp_test_20090815
 
Export: Release 10.2.0.1.0 - 64bit Production on Saturday, 15 August, 2009 15:05:35
 
Copyright (c) 2003, 2005, Oracle.  All rights reserved.
 
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
 
Job: EXP_TEST_20090815
  Owner: ROCKEY                         
  Operation: EXPORT                         
  Creator Privs: FALSE                          
  GUID: 7128C430B6C038D3E04400163584A0F7
  Start Time: Saturday, 15 August, 2009 15:05:36
  Mode: TABLE                          
  Instance: EMREP
  Max Parallelism: 1
  EXPORT Job Parameters:
  Parameter Name      Parameter Value:
     CLIENT_COMMAND        parfile=/oradata/exp/expdp.par logfile=expdp14540.log 
  State: IDLING                         
  Bytes Processed: 1,953,606,816
  Percent Done: 99
  Current Parallelism: 1
  Job Error Count: 0
  Dump File: /oradata/exp/exp_test.dmp
    bytes written: 1,953,615,872
 
Worker 1 Status:
  State: UNDEFINED                      
 
Export> start_job
 
Export> status
 
Job: EXP_TEST_20090815
  Operation: EXPORT                         
  Mode: TABLE                          
  State: COMPLETING                     
  Bytes Processed: 1,953,606,817
  Percent Done: 100
  Current Parallelism: 1
  Job Error Count: 0
  Dump File: /oradata/exp/exp_test.dmp
    bytes written: 1,953,685,504
 
Worker 1 Status:
  State: WORK WAITING