Question: I an running impdp and it looks
like the import is hung. How do I monitor a running data pump
import job? I need to monitor the import in real-time and ensure
that the import is working and not broken.
Answer: Monitoring Oracle imports is tricky,
especially after the rows are added and Oracle is busy adding indexes,
constraints, and CBO statistics. At this stage, the import looks
hung, but it's not stalled, it's working. You can monitor an Oracle import in
several ways:
- Monitor at the OS - Do a "ps -ef" on
the data pump process and watch it consume CPU. You can also
monitor the data pump log file with the "tail -f", command,
watching the progress of the import in real time. If you watch
the import log, be sure to include the feedback=1000 parameter to
direct import to display a dot every 1,000 lines of inserts.
- Monitor with the data pump views - The main
view to monitor import jobs are dba_datapump_jobs and
dba_datapump_sessions.
- Monitor with longops - You can query the
v$session_longops to see the progress of data pump, querying
the sofar and totalwork columns.
You can monitor an import with these basic queries. For a full
set of data pump import monitoring scripts see the
Oracle script
collection.
col
table_name format a30
select substr(sql_text, instr(sql_text,'"')+1,
instr(sql_text,'"', 1, 2)-instr(sql_text,'"')-1)
table_name,
rows_processed,
round((sysdate
- to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))
*24*60, 1) minutes,
trunc(rows_processed /
((sysdate-to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))
*24*60)) rows_per_min
from
v$sqlarea
where
upper(sql_text) like 'INSERT % INTO "%'
and
command_type = 2
and
open_versions >
0;
select
sid,
serial#
from
v$session s,
dba_datapump_sessions d
where
s.saddr = d.saddr;
select
sid,
serial#,
sofar,
totalwork
from
v$session_longops;
Also see:
Monitor data pump expdp
export script
See related data pump import articles:
Also see: Monitor data
pump expdp export script
and include the URL for the page.
