Datapump (expdp&impdp) is very useful tool while you need to take data from one DB to another or on the same database. So its very critical to know what is import status, what import do exact time etc.

In this post, I will share some useful SQL to can monitor data pump jobs.

— View the JOB_NAME using dba_datapump_jobs using below sql

SET lines 1000
COL owner_name FORMAT a10;
COL job_name FORMAT a20
COL state FORMAT a11
COL operation LIKE state
COL job_mode LIKE state
select * from dba_datapump_jobs where state='EXECUTING';

OWNER_NAME JOB_NAME             OPERATION J  OB_MODE     STATE       DEGREE      ATTACHED_SESSIONS DATAPUMP_SESSIONS
---------- -------------------- ----------- ----------- ----------- ----------   ----------------- -----------------
SYS        SYS_IMPORT_SCHEMA_01 IMPORT       SCHEMA     EXECUTING      1           1                   3

–Run the following query to monitor the progress by running the following SQLs.
SQL 1:

select sid, serial#, sofar, totalwork,
dp.owner_name, dp.state, dp.job_mode
from gv$session_longops sl, gv$datapump_job dp
where sl.opname = dp.job_name and sofar != totalwork;

SID    SERIAL#   SOFAR    TOTALWORK  OWNER_NAME   STATE     JOB_MODE
-----  -----   ---------- ---------- ---------- ----------  ----------- 
 178   3855    59513        59514      SYS       EXECUTING   SCHEMA

SQL 2:

SELECT SID, SERIAL#, opname, SOFAR, TOTALWORK,
ROUND(SOFAR/TOTALWORK*100,2) COMPLETE
FROM V$SESSION_LONGOPS
WHERE TOTALWORK != 0 AND SOFAR != TOTALWORK
order by 1;

SID         SERIAL#     OPNAME             SOFAR    TOTALWORK    COMPLETE
---------- ---------- ----------           -------   ---------   -------------
178         3855     SYS_IMPORT_SCHEMA_01  59513     59514         100
350         26965    Table Scan            459845    629857        73.01

— Check current job details 

select x.job_name,b.state,b.job_mode,b.degree
, x.owner_name,z.sql_text, p.message
, p.totalwork, p.sofar
, round((p.sofar/p.totalwork)*100,2) done
, p.time_remaining
from dba_datapump_jobs b 
left join dba_datapump_sessions x on (x.job_name = b.job_name)
left join v$session y on (y.saddr = x.saddr)
left join v$sql z on (y.sql_id = z.sql_id)
left join v$session_longops p ON (p.sql_id = y.sql_id)
WHERE y.module='Data Pump Worker'
AND p.time_remaining > 0;
Reklam