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
技术交流，技术讨论，欢迎加入
Technology Blog Created By Oracle ERP - 鲍新建
