73 lines
2.1 KiB
SQL
73 lines
2.1 KiB
SQL
@@header
|
|
|
|
/*
|
|
*
|
|
* Author : Vishal Gupta
|
|
* Purpose : Display long running sessions
|
|
* Compatibility : 10.1 and above
|
|
* Parameters : 1 - Number of minutes
|
|
* 2 - Number of rows to display
|
|
*
|
|
* Revision History:
|
|
* ===================
|
|
* Date Author Description
|
|
* --------- ------------ -----------------------------------------
|
|
* 05-Aug-04 Vishal Gupta First Draft
|
|
* 27-Mar-12 Vishal Gupta Included the session wait history
|
|
*
|
|
*/
|
|
|
|
define minutes=&="&&1"
|
|
define no_of_rows=&&2
|
|
|
|
|
|
COLUMN INST_ID HEADING "I#" FORMAT 99
|
|
COLUMN SID FORMAT 9999
|
|
COLUMN last_call_et HEADING "Last|Call|(minutes)" FORMAT 999,999
|
|
COLUMN event HEADING "EventName" FORMAT a33
|
|
COLUMN username FORMAT a20
|
|
COLUMN logon_time FORMAT a15
|
|
COLUMN service_name FORMAT a21
|
|
COLUMN process FORMAT a15
|
|
COLUMN osuser FORMAT a20 TRUNCATE
|
|
COLUMN machine FORMAT a30 TRUNCATE
|
|
COLUMN program FORMAT a50 TRUNCATE
|
|
|
|
|
|
|
|
Prompt ######################################################
|
|
Prompt L O N G R U N N I N G S E S S I O N S
|
|
Prompt
|
|
Prompt - Running Longer than &minutes minutes.
|
|
Prompt - displaying top &no_of_rows rows.
|
|
Prompt ######################################################
|
|
|
|
|
|
select * From
|
|
(
|
|
select s.inst_id
|
|
, s.sid
|
|
, s.status
|
|
, ROUND(s.last_call_et,2) last_call_et
|
|
, s.event
|
|
, s.sql_id
|
|
, s.username
|
|
, s.osuser
|
|
, s.program
|
|
, s.machine
|
|
from gv$session s
|
|
where last_call_et > (60 * &minutes)
|
|
and s.type <> 'BACKGROUND'
|
|
AND s.status = 'ACTIVE'
|
|
order by s.last_call_et desc
|
|
)
|
|
where rownum <= &no_of_rows
|
|
/
|
|
|
|
|
|
UNDEFINE minutes
|
|
UNDEFINE no_of_rows
|
|
|
|
|
|
@@footer
|