Queries to Monitor Expdp Jobs Status

 

1. How to check the progress of  export or import Jobs

SQL > SELECT OWNER_NAME,JOB_NAME,OPERATION,JOB_MODE,STATE from DBA_DATAPUMP_JOBS;

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

3. To monitor executing jobs using dba_datapump_jobs view:

SQL > SELECT 

owner_name, 

job_name, 

operation, 

job_mode, 

state 

FROM 

dba_datapump_jobs

where state='EXECUTING';

4. To get the detail information like SID, Serial#, and % of completion:

SQL > 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;

5. To check the waiting status and wait event of the job waiting for:

SQL > 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;

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

7. The percentage of work done

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;

SQL> select COMMAND,STATE,WAIT_CLASS,EVENT,SECONDS_IN_WAIT from v$session where sid=57 and SERIAL#=40478;

To check the orphaned datapump jobs. For orphaned jobs the state will be NOT RUNNING.

SQL> SELECT owner_name, job_name, operation, job_mode,

state, attached_sessions FROM dba_datapump_jobs;

To check the alert log and query the DBA_RESUMABLE view.

SQL > select name, sql_text, error_msg from dba_resumable;

To kill the datapump jobs:

SQL > alter system kill session 'SID,SERIAL#' immediate;

Check running EXPDP status.

$ expdp system/******@CDB attach=SYS_EXPORT_SCHEMA_04

[oracle@ora]$ expdp system/******@CDB attach=SYS_EXPORT_SCHEMA_04

Export: Release 12.2.0.1.0 - Production on Wed Dec 1 14:11:21 2021

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

Job: SYS_EXPORT_SCHEMA_04

  Owner: SYSTEM

  Operation: EXPORT

  Creator Privs: TRUE

  GUID: ABSCDNMVJBK

  Start Time: Wednesday, 01 December, 2021 19:03:20

  Mode: SCHEMA

  Instance: CDB

  Max Parallelism: 0

  Timezone: +00:00

  Timezone version: 28

  Endianness: LITTLE

  NLS character set: AL32UTF8

  NLS NCHAR character set: AL16UTF16

  EXPORT Job Parameters:

  Parameter Name      Parameter Value:

     CLIENT_COMMAND        system/********@CDB schemas=SONU directory=SONU dumpfile=SONU.dmp logfile=SONU.log reuse_dumpfiles=true

     TRACE                 0

  State: DEFINING

  Bytes Processed: 0

  Job Error Count: 0

  Job heartbeat: 1

Export> status

Job: SYS_EXPORT_SCHEMA_04

  Operation: EXPORT

  Mode: SCHEMA

  State: DEFINING

  Bytes Processed: 0

  Job Error Count: 0

  Job heartbeat: 1

Export>

================================================================

References:-

http://www.runningoracle.com/product_info.php?products_id=390

DataPump Export/Import Hangs With "DEFINING" Status When Using A Directory On NFS Filesystem (Doc ID 2262196.1)

Data Pump Hanging When Exporting To NFS Location (Doc ID 434508.1)

=====================================================