Queries to Monitor Datapump Jobs

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 STATUScommand:-

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48

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:-

1	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:-

1 2 3 4 5

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:-

1 2 3 4

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:-

1 2 3 4 5 6 7 8 9 10 11 12

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) Use the following procedure and replace the JOB_OWNER & JOB_NAME as per your env. which you fetch from import.log:-

1 2 3 4 5 6 7 8

;;; Import: Release 12.1.0.2.0 - Production on Thu Jun 29 00:29:09 2017   Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved. ;;; Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options Master table "SYSTEM"."SYS_IMPORT_FULL_04" successfully loaded/unloaded

Here the JOB_OWNER is SYSTEM and JOB_NAME is SYS_IMPORT_FULL_04.

And below is the procedure:-

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28

SET SERVEROUTPUT ON DECLARE   ind NUMBER;                h1 NUMBER;                 percent_done NUMBER;       job_state VARCHAR2(30);    js ku$_JobStatus;          ws ku$_WorkerStatusList;   sts ku$_Status;          BEGIN h1 := DBMS_DATAPUMP.attach('JOB_NAME', 'JOB_OWNER'); dbms_datapump.get_status(h1,            dbms_datapump.ku$_status_job_error +            dbms_datapump.ku$_status_job_status +            dbms_datapump.ku$_status_wip, 0, job_state, sts); js := sts.job_status; ws := js.worker_status_list;       dbms_output.put_line('*** Job percent done = ' ||                            to_char(js.percent_done));       dbms_output.put_line('restarts - '||js.restart_count); ind := ws.first;   while ind is not null loop     dbms_output.put_line('rows completed - '||ws(ind).completed_rows);     ind := ws.next(ind);   end loop; DBMS_DATAPUMP.detach(h1); end; /

7) Also for any errors you can check the alert log and query the DBA_RESUMABLE view.

1	select name, sql_text, error_msg from dba_resumable;

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.