How do you monitor Expdp Impdp process?

This article is all about monitoring oracle DataPump jobs, checking expdp or impdp jobs status, killing running jobs,
troubleshoot hang jobs, etc. Here you will get different queries, which you can use to start, stop, resume, kill and see the status of data pump jobs. So, let's monitor the Monitor Expdp Datapump jobs.

During the export or import job is in progress, you can press +C keys to get to the respective data pump prompt or you can attach to the running job and then run the STATUS command.

Read more about datapump: Click here or Subscribe on YouTube

How do I use query in Expdp?

Query to monitor running data pump jobs using dba_datapump_jobs view.

set linesize 250
set pagesize 250
col owner_name format a14
col job_name format a23
col operation format a13
col job_mode format a25

SELECT owner_name, job_name, operation, job_mode, state 
FROM dba_datapump_jobs
where state='EXECUTING';

Find more details about user sessions like SID, Serial#, and % of completion:

SELECT OPNAME, SID, SERIAL#, CONTEXT, SOFAR, TOTALWORK, ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE"
FROM V$SESSION_LONGOPS WHERE OPNAME in
( select d.job_name from v$session s, v$process p, dba_datapump_sessions d
where p.addr=s.paddr and s.saddr=d.saddr )
AND OPNAME NOT LIKE '%aggregate%' AND 
TOTALWORK != 0 AND SOFAR <> TOTALWORK;

Check to wait for status and wait for the event of the job waiting for:

SELECT   w.sid, w.event, w.seconds_in_wait
   FROM   V$SESSION s, DBA_DATAPUMP_SESSIONS d, V$SESSION_WAIT w
    WHERE   s.saddr = d.saddr AND s.sid = w.sid;

To check wait class for a particular SID:

SQL> select COMMAND,STATE,WAIT_CLASS,EVENT,SECONDS_IN_WAIT from v$session where sid=12 and SERIAL#=21;

Monitor running jobs status and other operations:

$ expdp attach=Job_name
expdp / as sysdba attach=job_name
export>status
export>stop_job
export>start_jop
export>kill_job

To check the orphaned oracle datapump jobs. For orphaned jobs, the state will be NOT RUNNING.

SET lines 141
COL owner_name FORMAT a12;
COL job_name FORMAT a22
COL state FORMAT a12 
COL operation LIKE owner_name
COL job_mode LIKE owner_name

SELECT owner_name, job_name, operation, job_mode, state, attached_sessions
FROM dba_datapump_jobs;

Check the database alert log and query the DBA_RESUMABLE view.

select name, sql_text, error_msg from dba_resumable;

How to delete/remove non executing datapump jobs?

Sometimes, we need to delete datapump jobs which are stopped abnormally due to some reason. The below steps will help us to find these types of jobs.

So, First, we need to check the details of not running jobs using the below query with the help of dba_datapump_jobs views.

SQL> SELECT owner_name, job_name, operation, job_mode, state, attached_sessions
     FROM dba_datapump_jobs ORDER BY 1,2;

Output from the above query:

In the above output, you can see there are three jobs whose status is showing not running.

SQL> SELECT o.status, o.object_id, o.object_type, o.owner||'.'||object_name "OWNER.OBJECT"
     FROM dba_objects o, dba_datapump_jobs j
     WHERE o.owner=j.owner_name AND o.object_name=j.job_name
     AND j.job_name NOT LIKE 'BIN$%' ORDER BY 4,2;

Output from the above query:

Now, you can drop these master tables using the drop command.

SQL> DROP TABLE SCOTT.EXPDP_1002111;
SQL> DROP TABLE SCOTT.SYS_EXPORT_TABLE_02;
SQL> DROP TABLE SYSTEM.SYS_EXPORT_FULL_01;

I hope now you understand how to Monitor Expdp Datapump jobs. If yes please write your fillings in the comment box.