How To Monitor The Progress Of Datapump Jobs (Doc ID 1471766.1)

In this Document

APPLIES TO: 

GOAL

The purpose of this document is to demonstrate how to monitor the progress of Datapump jobs.

SOLUTION

The progress can be monitored from either the Datapump client, or from within the database itself. For the demonstration it is assumed that EXPDP is being run. The same applies to IMPDP as well, but for all references to export then import should be replaced.

The progress can be monitored by:

1. The exporting session itself:
   
   Press the <Ctrl>+C keys, and from the prompt appearing, issue the STATUS command, like in:
   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: <PATH>\<DUMP_NAME>.DMP
       bytes written: 4,096
   
   Worker 1 Status:
     Process Name: DW00
     State: EXECUTING
     Object Schema: <SCHEMA_NAME>
     Object Name: <OBJECT_NAME>
     Object Type: DATABASE_EXPORT/SCHEMA/PACKAGE_BODIES/PACKAGE/PACKAGE_BODY
     Completed Objects: 78
     Worker Parallelism: 1

   
   This provides details on the status of the DM and DW processes involved in the export operation.
    

2. An exporting session attaching to the current Datapump job:
   
   When the job name of the EXPDP session is known, then the same information as above can be obtained from a new EXPDP session, as in:
   SQL> select * from dba_datapump_jobs;
   
   OWNER_NAME                     JOB_NAME
   ------------------------------ ------------------------------
   OPERATION                      JOB_MODE
   ------------------------------ ------------------------------
   STATE                              DEGREE ATTACHED_SESSIONS DATAPUMP_SESSIONS
   ------------------------------ ---------- ----------------- -----------------
   SYS                            SYS_EXPORT_FULL_01
   EXPORT                         FULL
   EXECUTING                               1                 1                 3
   
   
   -- Then from a command line session:
   
   EXPDP ATTACH=SYS_EXPORT_FULL_01 
   Job: SYS_EXPORT_FULL_01
     Owner: SYS
     Operation: EXPORT
     Creator Privs: TRUE
     GUID: 6F9FF0B3CF274562A56417E82A935987
     Start Time: Thursday, 12 July, 2012 12:56:52
     Mode: FULL
     Instance: <SID>
     Max Parallelism: 1
     EXPORT Job Parameters:
     Parameter Name      Parameter Value:
        CLIENT_COMMAND        /******** AS SYSDBA directory=d full=y
     State: EXECUTING
     Bytes Processed: 0
     Current Parallelism: 1
     Job Error Count: 0
     Dump File: <PATH>\<DUMP_NAME>.DMP
     bytes written: 4,096
   
   Worker 1 Status:
     Process Name: DW00
     State: EXECUTING
     Object Schema: <SCHEMA_NAME>
     Object Name: <OBJECT_NAME>
     Object Type: DATABASE_EXPORT/SCHEMA/PACKAGE_BODIES/PACKAGE/PACKAGE_BODY
     Completed Objects: 320
     Worker Parallelism: 1
3. Queries against the data dictionary:
   
   The DBA_DATAPUMP_JOBS view shows whether EXPDP or IMPDP jobs are still running, or have terminated with either a success or failure status. The data dictionary view V$SESSION_LONGOPS can be queried to get more detailed information on how much work is done already by the exporting job. E.g. the following queries can be used for this:
    
   1. The status of the job:
      SQL> select * from dba_datapump_jobs;
      
      OWNER_NAME                     JOB_NAME
      ------------------------------ ------------------------------
      OPERATION                      JOB_MODE
      ------------------------------ ------------------------------
      STATE                              DEGREE ATTACHED_SESSIONS DATAPUMP_SESSIONS
      ------------------------------ ---------- ----------------- -----------------
      SYS                            SYS_EXPORT_FULL_01
      EXPORT                         FULL
      EXECUTING                               1                 1                 3
   2. The percentage of work done, like in:
      SQL> 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;
      
      USERNAME                             SID
      ------------------------------ ----------
      OPNAME
      ----------------------------------------------------------------
      TARGET
      ----------------------------------------------------------------
      %DONE                                     TIME_REMAINING START_TIME
      ----------------------------------------- -------------- -------------------
      SYS                                   140
      SYS_EXPORT_FULL_01
      
      0%                                                       2012/07/12 12:57:21
   3. The percentage of work done and the current status of the Datapump job, like in:
      SQL> 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;
      
             SID    SERIAL#      SOFAR  TOTALWORK OWNER_NAME
      ---------- ---------- ---------- ---------- ------------------------------
      STATE                          JOB_MODE
      ------------------------------ ------------------------------
             140          9          0         67 SYS
      EXECUTING                      FULL

4. The following Query displays the amount of work done so far:

For Example:

SQL> SELECT sl.sid, sl.serial#, sl.sofar, sl.MESSAGE,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 2 3 ;

SID SERIAL# SOFAR MESSAGE TOTALWORK OWNER_NAME STATE JOB_MODE
---------- ---------- ---------- -------------------- ---------- ------------------------------ ------------------------------  
235 39132 25091 SYS_IMPORT_TABLE_01: 57907 SYS XXX EXECUTING TABLE

         IMPORT : 25091 out of 57907 MB done