PLSQL_数据泵导入进度查看Impdp/Expdp Status（案例）

20150701 Created By BaoXinjian

一、摘要

查看EXPDP/IMPDP的进度，当你当如导出的时候，如果数据量比较大，中途有些人会着急，不免想看看进度如何

1. 两个视图

DBA_DATAPUMP_JOBS;

DBA_DATAPUMP_SESSIONS;

2. Expdp / Impdp 控制台查看进程状态

二、编写Par调用Job

1. Expdp 案例

(1). 编写Expdp Par文件

[ERPPROD]oracle@gavinprod $ cat expdp_inv_mtl.par
schemas=INV
directory=inv_migrate_dir
job_name=inv_migrate_exp_job
logfile=tmp_log:expdp_inv_mtl.log
estimate=blocks
parallel=32
dumpfile=expdp_inv_mtl_%U.dmp

(2). 调用Expdp Par文件

[ERPPROD]oracle@gavinprod $  nohup expdp / parfile=expdp_inv_mtl.par 2>&1 &

2. Impdp案例

(1). 编写Impdp Par文件

[ERPPROD]oracle@gavinprod $ cat impdp_inv_mtl.par
tables=inv.mtl_material_transactions
directory=inv_migration_dir
job_name=inv_mtl_migrateion_job
logfile=tmp_log:impdp_inv_mtl.log
table_exists_action=replace
parallel=192
dumpfile=impdp_inv_mtl_%U.dmp

(2). 调用Impdp Par文件

[ERPPROD]oracle@gavinprod $  nohup impdp / parfile=impdp_inv_mtl.par 2>&1 &

三、查看进度

1. 查看视图

(1). DBA_DATAPUMP_JOBS

SQL> select * from DBA_DATAPUMP_JOBS;

OWNER_NAME JOB_NAME                        OPERATION  JOB_MODE   STATE                          DEGREE ATTACHED_SESSIONS DATAPUMP_SESSIONS
---------- -------------------- ---------- ---------- -------------------- ---------------- ----------------- -----------------
ORACLE     INV_MTL_MIGRATION_JOB           IMPORT     TABLE      EXECUTING                         192                 2                 4

(2). DBA_DATAPUMP_SESSIONS

SQL> select * from DBA_DATAPUMP_SESSIONS;

OWNER_NAME JOB_NAME                           INST_ID SADDR            SESSION_TYPE
---------- -------------------- ---------- ---------------- --------------
ORACLE     INV_MTL_MIGRATION_JOB                    1 0000000BD90D5DD8 DBMS_DATAPUMP
ORACLE     INV_MTL_MIGRATION_JOB                    1 0000000BE113F7A8 MASTER
ORACLE     INV_MTL_MIGRATION_JOB                    1 0000000BD118F530 WORKER
ORACLE     INV_MTL_MIGRATION_JOB                    1 0000000C18ED4440 DBMS_DATAPUMP

2. 查看Impdp控制台job status

[ERPPRD]oracle@gavinprod $ impdp / attach=inv_mtl_migrateion_job

Import: Release 11.2.0.4.0 - Production on Wed Jul 1 13:18:03 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

Job: inv_mtl_migrateion_job
  Owner: ORACLE
  Operation: IMPORT
  Creator Privs: TRUE
  GUID: 19CF12B2920E6FE0E053155FD80A299D
  Start Time: Wednesday, 01 July, 2015 11:49:16
  Mode: TABLE
  Instance: PROD
  Max Parallelism: 192
  EXPORT Job Parameters:
     CLIENT_COMMAND        /******** parfile=impdp_inv_mtl.par*/
     ESTIMATE              BLOCKS
  IMPORT Job Parameters:
  Parameter Name      Parameter Value:
     CLIENT_COMMAND        /******** parfile=impdp_inv_mtl.par*/
     TABLE_EXISTS_ACTION   REPLACE
  State: EXECUTING
  Bytes Processed: 0
  Current Parallelism: 192
  Job Error Count: 0
  Dump File: +RECO_DG/prod/inv_migration/impdp_inv_mtl_%u.dmp
  Dump File: +RECO_DG/prod/inv_migration/impdp_inv_mtl_01.dmp
  Dump File: +RECO_DG/prod/inv_migration/impdp_inv_mtl_02.dmp
  Dump File: +RECO_DG/prod/inv_migration/impdp_inv_mtl_03.dmp
  Dump File: +RECO_DG/prod/inv_migration/impdp_inv_mtl_04.dmp
  Dump File: +RECO_DG/prod/inv_migration/impdp_inv_mtl_05.dmp

Worker 1 Status:
  Process Name: DW00
  State: EXECUTING
  Object Schema: INV
  Object Name: MTL_MATERIAL_TRANSACTIONS
  Object Type: SCHEMA_EXPORT/TABLE/TABLE_DATA
  Completed Objects: 1
  Completed Rows: 7,668,247
  Completed Bytes: 109,307,040,496
  Percent Done: 84
  Worker Parallelism: 1

3. 查看Unix进度

[ERPPROD]oracle@gavinprod $ ps -ef |grep imp
oracle   28639 24228  0 11:49 pts/2    00:00:00 impdp   parfile=impdp_inv_mtl.par
oracle   31932 15764  0 13:22 pts/1    00:00:00 grep imp

4. 产看log

Thanks and Regards