Let us we discuss How to monitor our data pump jobs in oracle11g?

*Datapump jobs are monitor into few views maintained within the Oracle instance(Datapump is running)

*These views are DBA_DATAPUMP_JOBS, DBA_DATAPUMP_SESSIONS, and V$SESSION_LOGOPS.

I)DBA_DATAPUMP_JOBS:

*This View will provide the current data pump jobs status (the job which has been done it 'll disappear) and unsuccessful job details.

*We can see USER_DATAPUMP_JOBS displays the Data Pump jobs owned by the current user. This view does not display the OWNER_NAME column.


SQL> select * from dba_datapump_jobs;

OWNER_NAME          JOB_NAME      OPERATION        JOB_MODE       STATE     DEGREE   ATTACHED_SESSIONS  DATAPUMP_SESSIONS
---------------- ------------   ----------------- ----------- ----------  --------- ------------------ -------------------
SYSTEM         SYS_EXPORT_FULL_01    EXPORT         FULL         EXECUTING    1                 1                 3

SYSTEM         SYS_EXPORT_SCHEMA_01   EXPORT        SCHEMA       NOT RUNNING  0                 0                0


 

Note:OWNER_NAME=User that initiated the job, JOB_NAME=user supplied name or system default generated name, OPERATION=export /Import, JOB_MODE=Mode of job, STATE=current status of job, DEGREE=No of workers are performing the operation, ATTACHED_SESSIONS=No of sessions are attached to this job, DATAPUMP_SESSIONS=No of Data Pump sessions participating in the job

 

II)DBA_DATAPUMP_SESSIONS:

*This view will identify the user sessions that are attached to a Data Pump job

*The information in this view is useful for determining why a stopped Data Pump operation has not gone away.


  

SQL> select * from dba_datapump_sessions;

OWNER_NAME         JOB_NAME           INST_ID   SADDR    SESSION_TYPE
--------------- ------------------- ---------- -------- --------------
SYSTEM           SYS_EXPORT_FULL_01      1      35D8EAF0 DBMS_DATAPUMP

SYSTEM           SYS_EXPORT_FULL_01      1      367F23FC MASTER

SYSTEM           SYS_EXPORT_FULL_01      1      35CAE090 WORKER

 

Note: OWNER_NAME & JOB_NAME is already explained above the next ones is SADDR= Address of the session attached to the job. Can be used with V$SESSION view

 

III)V$SESSION_LONGOPS

* This view helps how the data pump job is performing

*Basically gives you a progress indicator through the MESSAGE column.

 


  SQL> select username,opname,target_desc,sofar,totalwork,message from V$SESSION_LONGOPS;

USERNAME     OPNAME        TARGET_DESC        SOFAR   TOTALWORK  MESSAGE
--------- -------------- ----------------  ---------- ---------- --------------------------------------------------
SYSTEM    EXPORT_FULL_DB     EXPORT            188        188   EXPORT_FULL_DB: EXPORT : 188 out of 188 MB done

SYSTEM    SYS_EXPORT_FULL_01   EXPORT          161        161   SYS_EXPORT_FULL_01: EXPORT : 161 out of 161 MB done

SYSTEM    SYS_IMPORT_FULL_01  IMPORT            0          132    SYS_IMPORT_FULL_01: IMPORT : 0 out of 132 MB done




 

Note: OPNAME=description of the operation or Job name , TARGET_DESC=The operation which is currently performing, SOFAR=The units for work done, TOTALWORK=The total units of Work, MESSAGE= Statistics summary message

 

Source: Oracle  Website ,Google