How to check Export/Import data pump job status
Monitor export/Import with the data pump views – The main view to monitor Export/Import jobs are dba_datapump_jobs and dba_datapump_sessions.
set line 190 col OWNER_NAME for a30 col JOB_NAME for a30 col OPERATION for a20 col JOB_MODE for a20 select OWNER_NAME,JOB_NAME,OPERATION,JOB_MODE,STATE from dba_datapump_jobs;
This view will show the active Data Pump jobs, their state, degree of parallelism, and the number of sessions attached
select * from dba_datapump_jobs;
OWNER_NAME JOB_NAME OPERATION JOB_MODE STATE DEGREE ATTACHED_SESSIONS
———- ———————- ———- ———- ————- ——— —————–
PYADAV SYS_EXPORT_FULL_01 EXPORT FULL EXECUTING 1 1
PYADAV SYS_EXPORT_SCHEMA_01 EXPORT SCHEMA EXECUTING 1 1
–
DBA_DATAPUMP_SESSIONS
This view assist in determining why a Data Pump session may be having problems. Join to the V$SESSION view for further information.
SQL> SELECT * FROM DBA_DATAPUMP_SESSIONS
$SESSION_LONGOPS
This view helps to determine how well a Data Pump export is working. Basically gives you a progress indicator through the MESSAGE column.
SQL> select username,opname,target_desc,sofar,totalwork,message from V$SESSION_LONGOPS USERNAME OPNAME TARGET_DES SOFAR TOTALWORK MESSAGE -------- -------------------- ---------- ----- ---------- ------------------------------------------------ PYADAV SYS_EXPORT_FULL_01 EXPORT 132 132 SYS_EXPORT_FULL_01:EXPORT:132 out of 132 MB done PYADAV SYS_EXPORT_FULL_01 EXPORT 90 132 SYS_EXPORT_FULL_01:EXPORT:90 out of 132 MB done PYADAV SYS_EXPORT_SCHEMA_01 EXPORT 17 17 SYS_EXPORT_SCHEMA_01:EXPORT:17 out of 17 MB done PYADAV SYS_EXPORT_SCHEMA_01 EXPORT 19 19 SYS_EXPORT_SCHEMA_01:EXPORT:19 out of 19 MB done
Now you can find the username and job name with above command and then execute below command to check status how much export/Import Job completed
expdp \”/ as sysdba\” attach=SYS_EXPORT_SCHEMA_01