<- Scripts
-- dp.status.sql
-- Purpose: Data Pump status.
-- To attach to a job: [exp|imp]dp attach=[job_name] |
SET LINESIZE 300
SET PAGESIZE 9999
SET FEEDBACK OFF
-- dba_datapump_jobs
COL owner_name FORMAT a10 HEADING 'Owner'
COL job_name FORMAT a20
COL job_mode FORMAT a12
COL state FORMAT a12
COL degree FORMAT 99
COL attached_sessions FORMAT 99
COL datapump_sessions FORMAT 99
-- dba_datapump_sessions
COL saddr FORMAT 9999999999
COL session_type FORMAT a15
-- dba_datapump_sessions 2
COL sid FORMAT 99999 HEADING 'SID'
COL serial# FORMAT 99999 HEADING 'SN'
COL opname FORMAT a15 HEADING 'Operation'
COL target FORMAT a26 HEADING 'Target'
COL sofar FORMAT 999,999,999 HEADING 'So Far'
COL totalwork FORMAT 999,999,999 HEADING 'Total Wrk'
COL units FORMAT a6 HEADING 'Units'
COL elapsed_seconds FORMAT 999999 HEADING 'Elapse'
COL time_remaining FORMAT 999999
COL message FORMAT a50
clear screen
PROMPT +------------------+
PROMPT | Data Pump Status |
PROMPT +------------------+
PROMPT
-- dba_datapump_jobs
PROMPT [dba_datapump_jobs]
COL owner_name FORMAT a10;
COL job_name FORMAT a20
COL state FORMAT a12
COL operation LIKE owner_name
COL job_mode LIKE owner_name
SELECT owner_name, job_name, operation, job_mode,
state, attached_sessions
FROM dba_datapump_jobs;
PROMPT
-- v$session_longops
PROMPT [v$session_longops]
SELECT to_char(start_time,'yyyy/mm/dd hh24:mi') Started,sid,serial#, opname, sofar, totalwork,
units, elapsed_seconds, message
FROM v$session_longops
WHERE message LIKE '%EXPORT%' OR message LIKE '%IMPORT%'
AND start_time > sysdate-3
ORDER BY start_time DESC;
PROMPT
-- dba_datapump_jobs, dba_datapump_sessions
PROMPT [dba_datapump_jobs, dba_datapump_sessions]
SELECT j.owner_name, j.job_name, j.job_mode, j.state, s.session_type, s.saddr
FROM dba_datapump_jobs j,dba_datapump_sessions s
WHERE UPPER(j.job_name) = UPPER(s.job_name);
PROMPT
-- v$sess_io, v$session, dba_datapump_sessions (ALL)
PROMPT [v$sess_io, v$session, dba_datapump_sessions (ALL)]
SELECT v.status, v.sid,v.serial#,io.block_changes,event
FROM v$sess_io io, v$session v
WHERE io.sid = v.sid
AND v.saddr in (select saddr from dba_datapump_sessions) order by sid;
PROMPT
-- v$sess_io, v$session, dba_datapump_sessions (not waiting)
PROMPT [v$sess_io, v$session, dba_datapump_sessions (not waiting)]
SELECT v.status, v.sid,v.serial#,io.block_changes,event
FROM v$sess_io io, v$session v
WHERE io.sid = v.sid
AND v.saddr in (select saddr from dba_datapump_sessions)
AND event not like '%wait%'
ORDER BY sid;
