59 lines
2.5 KiB
SQL
59 lines
2.5 KiB
SQL
@@header
|
|
|
|
/*
|
|
*
|
|
* Author : Vishal Gupta
|
|
* Purpose : Display datapump sessions
|
|
*
|
|
* Revision History:
|
|
* ===================
|
|
* Date Author Description
|
|
* --------- ------------ -----------------------------------------
|
|
* 24-Mar-12 Vishal Gupta First Draft
|
|
*/
|
|
|
|
COLUMN owner_name HEADING "OwnerName" FORMAT a15
|
|
COLUMN job_name HEADING "JobName" FORMAT a30
|
|
COLUMN job_mode HEADING "JobMode" FORMAT a9
|
|
COLUMN operation HEADING "Oper" FORMAT a6
|
|
COLUMN workers HEADING "Job|Workers" FORMAT 9999
|
|
COLUMN session_type HEADING "SessionType" FORMAT a15
|
|
COLUMN pct_done HEADING "%Done" FORMAT 999.99
|
|
COLUMN sid HEADING "SID" FORMAT 99999
|
|
COLUMN inst_id HEADING "I#" FORMAT 99
|
|
COLUMN spid HEADING "SPID" FORMAT a6
|
|
COLUMN status HEADING "Session|Status" FORMAT a8
|
|
COLUMN last_call_et HEADING "LastCall|Active|(sec)" FORMAT 99,999
|
|
COLUMN logon_time HEADING "LogonTime" FORMAT a18
|
|
COLUMN username HEADING "DBUser" FORMAT a15
|
|
COLUMN osuser HEADING "OSUser" FORMAT a15 TRUNC
|
|
COLUMN machine HEADING "Machine" FORMAT a20 TRUNC
|
|
|
|
select d.owner_name || '.' || d.job_name job_name
|
|
, j.job_mode
|
|
, j.operation
|
|
, j.workers
|
|
, d.session_type || ' ' || DECODE(d.session_type,'WORKER',SUBSTR(s.program,INSTR(s.program,'(')+3,2),'') session_type
|
|
, ROUND(l.sofar / l.totalwork *100, 2) pct_done
|
|
, s.sid
|
|
, s.inst_id
|
|
, p.spid
|
|
, s.status
|
|
, s.last_call_et
|
|
, to_char(s.logon_time,'DD-MON-YY HH24:MI:SS') logon_time
|
|
, s.username
|
|
, s.osuser
|
|
, s.machine
|
|
from dba_datapump_sessions d
|
|
JOIN gv$datapump_job j ON j.inst_id = d.inst_id AND j.owner_name = d.owner_name AND j.job_name = d.job_name
|
|
LEFT OUTER JOIN gv$session s ON s.inst_id = d.inst_id AND s.saddr = d.saddr
|
|
LEFT OUTER JOIN gv$process p ON s.inst_id = p.inst_id AND s.paddr = p.addr
|
|
LEFT OUTER JOIN gv$session_longops l ON l.inst_id = s.inst_id AND l.sid = s.sid AND l.time_remaining > 0
|
|
ORDER BY 1
|
|
, s.logon_time
|
|
, d.session_type || ' ' || DECODE(d.session_type,'WORKER',SUBSTR(s.program,INSTR(s.program,'(')+3,2),'')
|
|
;
|
|
|
|
@@footer
|
|
|