oracledba.help
Scripts

runDPScript.sh

<- 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;