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:
set pages 200
set lines 300
col USERNAME for a15
col TARGET for a15
col OPNAME for a20
col "%DONE" for a7
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;
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;
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;
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!!
