How to Attach to a Datapump Job and Check Status of Export or Import

<<Back to Oracle DATAPUMP Main Page

How to check the progress of  export or import Jobs

You can attach to the export/import  job using ATTACH parameter of oracle datapump utility. Once you are attached to the job you check its status by typing STATUS command.
Let us see how
Step1>  Find the Export/Import Job Name

You can find the datapump job information from DBA_DATAPUMP_JOBS or USER_DATAPUMP_JOBS view.

SQL> SELECT OWNER_NAME,JOB_NAME,OPERATION,JOB_MODE,STATE from DBA_DATAPUMP_JOBS;
OWNER_NAME JOB_NAME                       OPERATION            JOB_MODE   STATE
---------- ------------------------------ -------------------- ---------- ----------
SYSTEM     SYS_EXPORT_FULL_02             EXPORT               FULL       EXECUTING

OR
You can also find the job name for export/import in logfile in beginning itself.

Step2>Attach to the Job and check status
One you get the Export/Import Job Name attach the job and check its status. You can attach or detach  or reattach to the job anytime  you want as long as the job exists.

$ expdp system@PDB01 attach=SYSTEM.SYS_EXPORT_FULL_02
Export: Release 12.2.0.1.0 - Production on Wed Oct 10 10:56:00 2018
Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.
Password:
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Job: SYS_EXPORT_FULL_02
  Owner: SYSTEM
  Operation: EXPORT
  Creator Privs: TRUE
  GUID: 77DD035E75392621E0530601A8C09C5B
  Start Time: Wednesday, 10 October, 2018 10:55:17
  Mode: FULL
  Instance: TST2T
  Max Parallelism: 1
  Timezone: +02:00
  Timezone version: 26
  Endianness: LITTLE
  NLS character set: AL32UTF8
  NLS NCHAR character set: AL16UTF16
  EXPORT Job Parameters:
  Parameter Name      Parameter Value:
     CLIENT_COMMAND        system/********@PDB01 parfile=exp_full_pdb01.par
     TRACE                 0
  State: EXECUTING
  Bytes Processed: 0
  Current Parallelism: 1
  Job Error Count: 0
  Job heartbeat: 1
  Dump File: /u01/dbatst2/stage/dump/EXP_PDB01_FULL01.dmp
    bytes written: 114,688
  Dump File: /u01/dbatst2/stage/dump/EXP_PDB01_FULL%u.dmp
Worker 1 Status:
  Instance ID: 1
  Instance name: TST2T
  Host name: test01
  Object start time: Wednesday, 10 October, 2018 10:55:55
  Object status at: Wednesday, 10 October, 2018 10:55:55
  Process Name: DW00
  State: EXECUTING

Finding the Datapump Job Status from V$SESSION_LONGOPS

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    
  AND B.OPNAME LIKE '%EXPORT%'
   ORDER BY 6;