95 lines
2.9 KiB
SQL
95 lines
2.9 KiB
SQL
@@header
|
|
|
|
/*
|
|
*
|
|
* Author : Vishal Gupta
|
|
* Purpose : Generate kill session list
|
|
* Parameters : 1 - Where clause to filter the data.
|
|
*
|
|
* Revision History:
|
|
* ===================
|
|
* Date Author Description
|
|
* --------- ------------ -----------------------------------------
|
|
* 26-Jul-12 Vishal Gupta Created
|
|
*
|
|
*/
|
|
|
|
|
|
|
|
/************************************
|
|
* INPUT PARAMETERS
|
|
************************************/
|
|
DEFINE WHERE_CLAUSE="&1"
|
|
|
|
|
|
|
|
/************************************
|
|
* CONFIGURATION PARAMETERS
|
|
************************************/
|
|
-- To hide sql, set show_sql="--"
|
|
--DEFINE SHOW_SQL="--"
|
|
DEFINE SHOW_SQL=""
|
|
DEFINE SHOW_BACKGROUND=""
|
|
|
|
|
|
PROMPT **************************************************************
|
|
PROMPT * D A T A B A S E S E S S I O N S
|
|
PROMPT *
|
|
PROMPT * Input Parameters
|
|
PROMPT * - Where Clause = "&&WHERE_CLAUSE"
|
|
PROMPT **************************************************************
|
|
|
|
COLUMN Kill_command HEADING "KillCommand" FORMAT a22
|
|
COLUMN sid HEADING "SID" FORMAT 99999
|
|
COLUMN inst_id HEADING "I#" FORMAT 99
|
|
COLUMN spid HEADING "SPID" FORMAT a6
|
|
COLUMN status HEADING "Status" FORMAT a8
|
|
COLUMN state HEADING "State" FORMAT a12 TRUNCATED
|
|
COLUMN logon_time HEADING "Logon Time" FORMAT a18
|
|
COLUMN username HEADING "UserName" FORMAT a20
|
|
COLUMN osuser HEADING "OS User" FORMAT a15 TRUNCATED
|
|
COLUMN MACHINE HEADING "Machine" FORMAT a20 TRUNCATED
|
|
COLUMN process HEADING "Process" FORMAT a11
|
|
COLUMN program HEADING "Program" FORMAT a18 TRUNCATED
|
|
COLUMN event HEADING "Event" FORMAT a30 TRUNCATED
|
|
--COLUMN last_call_et HEADING "LastCall|(sec)" FORMAT 999,999
|
|
COLUMN last_call_et HEADING "LastCall" FORMAT a12
|
|
COLUMN sql_child_number HEADING "SQL|Child|No" FORMAT 99
|
|
|
|
SELECT '@kill_session ' || s.sid || ' ' || s.inst_id Kill_command
|
|
, s.sid
|
|
, s.inst_id
|
|
, p.spid
|
|
, s.status
|
|
--, s.state
|
|
--, s.last_call_et
|
|
, LPAD(FLOOR(last_call_et / 3600) || 'h '
|
|
|| LPAD(FLOOR(MOD(last_call_et , 3600 ) / 60),2) || 'm '
|
|
|| LPAD(MOD(last_call_et, 60 ) ,2) || 's'
|
|
, 12) last_call_et
|
|
, to_char(s.logon_time,'DD-MON-YY HH24:MI:SS') logon_time
|
|
, s.username
|
|
, s.osuser
|
|
, s.machine
|
|
, s.process
|
|
, s.program
|
|
, s.sql_id
|
|
, s.sql_child_number
|
|
FROM gv$session s
|
|
, gv$session_wait w
|
|
, gv$process p
|
|
WHERE s.sid = w.sid (+)
|
|
AND s.inst_id = w.inst_id (+)
|
|
AND s.inst_id = p.inst_id
|
|
AND s.paddr = p.addr
|
|
&&SHOW_BACKGROUND AND s.type <> 'BACKGROUND'
|
|
&&WHERE_CLAUSE
|
|
ORDER BY s.logon_time desc
|
|
;
|
|
|
|
UNDEFINE INST_ID
|
|
UNDEFINE WHERE_CLAUSE
|
|
|
|
|
|
@@footer
|