This post is to cover the queries that could be used to monitor the running Datapump Jobs.
Without wasting anytime just posting the methods/queries which can be used to monitor the details of the datapump jobs:-
1) Using the datapump client (expdp & impdp) STATUS command:-
When the export or import job is running press +C keys to get to the respective datapump client prompt OR you can use another session of datapump client and using the ATTACH clause attach to the running job and then issue the STATUS command:-
Export> status Job: SYS_EXPORT_FULL_01 Operation: EXPORT Mode: FULL State: EXECUTING Bytes Processed: 0 Current Parallelism: 1 Job Error Count: 0 Dump File: /u01/app/oracle/dpump/admin.dmp bytes written: 4,096 Worker 1 Status: Process Name: DW00 State: EXECUTING Object Schema: ADMIN Object Name: TEST_01 Object Type: DATABASE_EXPORT/SCHEMA/PACKAGE_BODIES/PACKAGE/PACKAGE_BODY Completed Objects: 78 Worker Parallelism: 1
Import> status Job: SYS_IMPORT_SCHEMA_01 Operation: IMPORT Mode: SCHEMA State: EXECUTING Bytes Processed: 2,788,707,576 Percent Done: 99 Current Parallelism: 6 Job Error Count: 0 Dump File: /apps/keplero/backup/ORA11G/dpump/cishd-34173_%u.dmp Dump File: /apps/keplero/backup/ORA11G/dpump/cishd-34173_01.dmp Dump File: /apps/keplero/backup/ORA11G/dpump/cishd-34173_02.dmp Dump File: /apps/keplero/backup/ORA11G/dpump/cishd-34173_03.dmp Dump File: /apps/keplero/backup/ORA11G/dpump/cishd-34173_04.dmp Dump File: /apps/keplero/backup/ORA11G/dpump/cishd-34173_05.dmp Dump File: /apps/keplero/backup/ORA11G/dpump/cishd-34173_06.dmp Worker 1 Status: Process Name: DW00 State: EXECUTING Object Schema: XTP_AC Object Name: SYS_C0063284986 Object Type: SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT Completed Objects: 1,120 Worker Parallelism: 1 Worker 2 Status: Process Name: DW01 State: WORK WAITING Worker 3 Status: Process Name: DW02 State: WORK WAITING Worker 4 Status: Process Name: DW03 State: WORK WAITING Worker 5 Status: Process Name: DW04 State: WORK WAITING Worker 6 Status: Process Name: DW05 State: WORK WAITING Import>
So from the output you can see the status of the Master Control Process and Worker process, read my post Oracle datapump Architecture & Internals for better understanding of Datapump Architecture.
2) Querying DBA_DATAPUMP_JOBS view:-
select * from dba_datapump_jobs;
The STATE column of the above view would give you the status of the JOB to show whether EXPDP or IMPDP jobs are still running, or have terminated with either a success or failure status.
3) Querying V$SESSION_LONGOPS & V$SESSION views:-
SELECT b.username, a.sid, b.opname, b.target, round(b.SOFAR*100/b.TOTALWORK,0) || '%' as "%DONE", b.TIME_REMAINING, to_char(b.start_time,'YYYY/MM/DD HH24:MI:SS') start_time FROM v$session_longops b, v$session a WHERE a.sid = b.sid ORDER BY 6;
4) Querying V$SESSION_LONGOPS & V$DATAPUMP_JOB views:-
SELECT sl.sid, sl.serial#, sl.sofar, sl.totalwork, dp.owner_name, dp.state, dp.job_mode FROM v$session_longops sl, v$datapump_job dp WHERE sl.opname = dp.job_name AND sl.sofar != sl.totalwork;
5) Querying all the related views with a single query:-
select x.job_name,b.state,b.job_mode,b.degree , x.owner_name,z.sql_text, p.message , p.totalwork, p.sofar , round((p.sofar/p.totalwork)*100,2) done , p.time_remaining from dba_datapump_jobs b left join dba_datapump_sessions x on (x.job_name = b.job_name) left join v$session y on (y.saddr = x.saddr) left join v$sql z on (y.sql_id = z.sql_id) left join v$session_longops p ON (p.sql_id = y.sql_id) WHERE y.module='Data Pump Worker' AND p.time_remaining > 0;
6) Also for any errors you can check the alert log and query the DBA_RESUMABLE view.
That’s all what I can think of at the moment, would add the queries to this post if I find another view which can be used to get the information of the datapump jobs.