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