Check progress on expdp and impdp:
In few cases we need to monitor the progress of an export or import job in oracle. below are the steps can be followed to get the details.
Datapump Client:
get the session details:
SQL> select * from dba_datapump_jobs where state='EXECUTING';
attach the session:
from Command line:
expdp system/********** attach=SYS_EXPORT_FULL_01
Export > status
From Database:
Query 1: The % of work done for the running job can be found from this query:
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;
Query 2: The work done so far and status of the job:
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;
Query 3: The amount of work done so far:
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;
Reference:
How To Monitor The Progress Of Datapump Jobs (Doc ID 1471766.1)
Best Wishes!!
