@@header

/*
*
*  Author     : Vishal Gupta
*  Purpose    : Display Session Details
*  Parameters : 1 - SPID    (OS Process ID)
*               2 - INST_ID (optional, default to 1)
*
*  Revision History:
*  ===================
*  Date       Author        Description
*  ---------  ------------  -----------------------------------------
*  05-Aug-04  Vishal Gupta  First Draft
*/


COLUMN INST_ID                HEADING "I#"              FORMAT 99
COLUMN SPID                                             FORMAT 99999
COLUMN SID                                              FORMAT 9999
COLUMN serial#                HEADING "Serial#"         FORMAT 99999
COLUMN username                                         FORMAT a20
COLUMN osuser                                           FORMAT a10
COLUMN machine                                          FORMAT a20
COLUMN logon_time                                       FORMAT a15
COLUMN service_name                                     FORMAT a21
COLUMN program                                          FORMAT a20 
COLUMN process                                          FORMAT a10
COLUMN last_call_et           HEADING "Last|Call|(s)"   FORMAT 99,999        


VARIABLE PID NUMBER
VARIABLE INST_ID NUMBER

BEGIN
    :PID := &&1;
	:INST_ID := NVL(&2,1);
	IF :INST_ID = '' OR :INST_ID IS NULL THEN
	   :INST_ID := 1;
	END IF;   
END;
/

Prompt
Prompt ################  Process Details ######################
Prompt

select LPAD(p.sPID,5,' ') SPID
     , s.SID
     , s.SERIAL#
     , s.process
     , s.username
     , s.osuser
     , s.status
	 , CASE
          WHEN s.status = 'ACTIVE' THEN s.last_call_et
          ELSE NULL
       END last_call_et
     , TO_CHAR(s.LOGON_TIME,'DD-MM-YY HH24:MI') logon_time
     , s.MACHINE
     , s.PROGRAM
from  gv$session s, gv$process p
where s.inst_id = p.inst_id
AND   s.PADDR = p.ADDR
AND   p.spid = :PID
AND   p.inst_id = :INST_ID
order by 1;

Prompt 
Prompt ######  Session Lock Information ############
Prompt

select s.blocking_instance
     , s.blocking_session
     , s.blocking_session_status
     , s.final_blocking_instance
     , s.final_blocking_session
     , s.final_blocking_session_status
FROM gv$session s, gv$process p
where s.inst_id = p.inst_id
AND   s.PADDR = p.ADDR
AND   p.spid = :PID
AND   p.inst_id = :INST_ID
order by 1;



Prompt
Prompt #######  Current SQL being executed #########
Prompt

SELECT sql.sql_text
FROM   gv$sqltext_with_newlines sql, gv$session s, gv$process p
WHERE  s.inst_id = sql.inst_id
AND    s.SQL_ADDRESS = sql.address
AND    s.SQL_HASH_VALUE = sql.hash_value
AND    s.inst_id = p.inst_id
AND    s.PADDR = p.ADDR
AND    p.spid  = :PID
AND    p.inst_id = :INST_ID
order by piece
/


Prompt
Prompt ################  Session Wait ######################
Prompt

COLUMN state  FORMAT a10
COLUMN event           HEAD "EventName"          FORMAT a35
COLUMN seconds_in_wait HEADING "Wait(s)"            FORMAT 99,999        
COLUMN p1  FORMAT 9999999999
COLUMN p2  FORMAT 9999999999
COLUMN p3  FORMAT 9999999999
COLUMN p1text  FORMAT a20 
COLUMN p2text  FORMAT a20 
COLUMN p3text  FORMAT a20 


SELECT w.inst_id
     , w.sid
     , w.seq#
     , w.state
     , w.event
     , w.seconds_in_wait
     , w.p1
     , w.p1text
     , w.p2
     , w.p2text
     , w.p3
     , w.p3text
FROM   gv$session_wait w, gv$session s, gv$process p
WHERE s.inst_id = p.inst_id
  AND s.PADDR = p.ADDR
  AND w.inst_id = s.inst_id
  AND w.sid = s.sid
  AND p.spid = :PID
  AND p.inst_id = :INST_ID
/


Prompt
Prompt #######  Session Events ####################
Prompt

COLUMN event           HEAD "EventName"          FORMAT a35
COLUMN total_waits     HEAD "TotalWaits"
COLUMN total_timeouts  HEAD "TotalTimeOuts"
COLUMN time_waited     HEAD "TimeWaited (s)"
COLUMN average_wait    HEAD "AvgWait (s)"
COLUMN max_wait        HEAD "MaxWait (s)"

SELECT e.inst_id
     , e.sid
     , e.event
     , e.total_waits
     , e.total_timeouts
     , e.time_waited / 100 time_waited
     , e.average_wait / 100 average_wait
     , e.max_wait    / 100 max_wait
FROM   gv$session_event e, gv$session s, gv$process p
WHERE  s.inst_id = p.inst_id
  AND s.PADDR = p.ADDR
  AND e.inst_id = s.inst_id
  AND e.sid = s.sid
  AND p.spid = :PID
  AND p.inst_id = :INST_ID
order by e.time_waited desc
/


Prompt
Prompt #######  Kill/Disconnect Command ####################

COLUMN command           HEADING "Disconnect Command"   FORMAT a60
COLUMN command2          HEADING "Kill Command"         FORMAT a60


select 'alter system disconnect session '''  || s.sid || ',' || s.serial# || ',@' || s.inst_id || ''' immediate ; ' Command
     , 'alter system kill session '''  || s.sid || ',' || s.serial# || ',@' || s.inst_id || ''' immediate ; '       Command2
from  gv$session s
    , gv$process p
where s.inst_id = p.inst_id 
AND   s.PADDR = p.ADDR 
AND   p.spid = :PID
AND   p.inst_id = :INST_ID
;


BEGIN
	:PID := NULL;
	:INST_ID := NULL;
END;
/

@@footer