57 lines
1.1 KiB
SQL
57 lines
1.1 KiB
SQL
--@open_cursors_count
|
|
-- Sid wise open cursors count.
|
|
SET echo off
|
|
SET lines 200
|
|
SET PAGES 50
|
|
|
|
|
|
COLUMN SID FORMAT 999
|
|
COLUMN SPID FORMAT 99999
|
|
COLUMN "max#" FORMAT a5
|
|
COLUMN username FORMAT a15
|
|
COLUMN osuser FORMAT a10
|
|
COLUMN machine FORMAT a20
|
|
COLUMN program FORMAT a30
|
|
|
|
|
|
select 'open_cursors'
|
|
, to_number(b.value) "max#"
|
|
, lpad(used, 5) "cur#"
|
|
, to_char(100 * used / value, '990') || '%' "Used%"
|
|
, s.SID
|
|
, p.SPID
|
|
, s.SERIAL#
|
|
, s.username
|
|
, s.osuser
|
|
, to_char(s.LOGON_TIME,'DD-MM-YY HH24:MI') Logon_time
|
|
, s.MACHINE
|
|
, s.PROGRAM
|
|
from
|
|
( select
|
|
s.sid
|
|
, sum(s.value) used
|
|
from
|
|
v$statname n,
|
|
v$sesstat s
|
|
where
|
|
n.name in ('opened cursors current', 'session cursor cache count') and
|
|
s.statistic# = n.statistic#
|
|
group by
|
|
s.sid
|
|
) a ,
|
|
( select
|
|
sum(value) value
|
|
from
|
|
v$parameter
|
|
where
|
|
name in ( 'open_cursors', 'session_cached_cursors')
|
|
) b ,
|
|
v$session s
|
|
, v$process p
|
|
where s.sid = a.sid
|
|
and s.paddr = p.addr
|
|
order by 4 asc
|
|
/
|
|
|
|
|