DB - How to monitor Oracle datapump jobs

These days there is lot of work around database migrations not only but mainly to cloud providers. One of the ways to migrate Oracle databases is using data pump (expdp/impdp).
In general works fine, it allows an easy way to bring the database even different OS.

It is always good to monitor the progress, to know where we are and estimate how long we are from the end.
In this post, I am sharing some queries to help with database monitoring progress. The queries can be easily tailed to various scenarios.

Starting with a simple one - the track the number of objects loaded during an import operation.

When there is a massive amount of errors during the import. Drop and run the import again.
The bellow query can be useful also to monitor the number of objects if we are dropping the users.

Here, I’m making the assumption there were no other database activity in progress on the last 24 hours.

select owner, count(0) from 
dba_objects where owner in ( select username from dba_users where created > sysdate - 1) group by owner;

During the import (impdp) , the log is written when the data is loaded is loaded into the table. That is an inconvenient when the dump files have large (non-partitioned) tables. A way to track the data load progress is to check the table size. That can be done checking the dba_segments.bytes data dictionary.
The next queries help with that.

select  sum(BYTES)/1024/1024/1024  GB 
from dba_segments  where owner in ( select username from dba_users where created > sysdate - 1);

-- same as previous but per owner
set lines 200 
col OWNER form a30
select  owner,sum(BYTES)/1024/1024/1024  from dba_segments  where owner in ( select username from dba_users where created > sysdate - 1)
group by owner;

During the activity of database data also monitor pump operations the sessions monitored.
Including (and mainly) if the impdp/expdp is killed at OS level.

set lines 200
col  MESSAGE form a70
col OWNER_NAME form a10
col JOB_MODE form a10
col STATE  form a10
 
SELECT sl.sid, sl.serial#, sl.sofar, sl.MESSAGE,sl.totalwork, dp.owner_name, dp.state, dp.job_mode,
round(sl.sofar*100/sl.totalwork,0) || '%' DONE
FROM v$session_longops sl, v$datapump_job dp
WHERE sl.opname = dp.job_name;

The monitoring is not only about keep the eye on the space. it’s important to resumable operations, mainly in import (impdp ) operations.

set lines 200
col  name form a30
col  sql_text form a100 
col error_msg form a20
select name, sql_text, error_msg from dba_resumable;
 
	 

Finally, it is important to monitor the instance events in all data pump operations, to identify possible need of instance locks or tuning.

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;