199 lines
4.8 KiB
SQL
199 lines
4.8 KiB
SQL
@@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
|
|
|
|
|