Being a DBA, you often asked to perform and monitor expdp/impdp - datapump jobs on regular basis. In this post you will get varieties of queries using which you can start, stop, resume, kill and see the status of data pump jobs.
When the export or import job is in progress, you can press +C keys to get to the respective datapump prompt or you can attach to the running job and then issue the STATUS command:
To monitor executing jobs using dba_datapump_jobs view:
set linesize 200 set pagesize 200 col owner_name format a12 col job_name format a20 col operation format a12 col job_mode format a20 SELECT owner_name, job_name, operation, job_mode, state FROM dba_datapump_jobs where state='EXECUTING';
To get the detail information 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;
To check the waiting status and wait 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 event event and wait class for a particular SID:
SQL> select COMMAND,STATE,WAIT_CLASS,EVENT,SECONDS_IN_WAIT from v$session where sid=7248 and SERIAL#=56639;
To monitor and perform various operations from the expdp/impdp prompt:
[oracle@orahow ~]$ 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 datapump jobs. For orphaned jobs the state will be NOT RUNNING.
SET lines 140 COL owner_name FORMAT a10; COL job_name FORMAT a20 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;
To check the alert log and query the DBA_RESUMABLE view.
select name, sql_text, error_msg from dba_resumable;
To kill the datapump jobs:
alter system kill session 'SID,SERIAL#' immediate;
That's all...if you have any suggestions or any other queries to monitor datapump jobs status, please comment us. We will definitely go through it and will include in this article.
