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