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

To BottomTo Bottom

In this Document

  Goal
  Solution

 

APPLIES TO: 

Oracle Database - Enterprise Edition - Version 10.1.0.2 and later
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Backup Service - Version N/A and later
Information in this document applies to any platform.

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:

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;

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