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

2 thoughts on “Queries to Monitor Datapump Jobs

  1. Pingback: Oracle datapump Internals | Oracle Database Internal Mechanism

  2. Pingback: Datapump Export (Everything need to know) | Oracle Database Internal Mechanism

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s