SQL Script TOP Cursor

Updated : 08-Jan-2002
Version : 1.0

Description

Script displays details of the sessions holding open the most cursors. It also allows you to drill down and look at the SQL.

Parameters

SID - The Session Identifer (from v$session)

SQL Source

col sid form 999 heading "SID"
col piece form 99 heading "P"
col count(*) form 9,999 heading "Count"
set linesize 200
set trimspool on

spool topcursor.lst

select rownum as rank, a.*
from (
    SELECT sid, user_name ,count(*)
    FROM v$open_cursor
    GROUP BY sid, user_name
   ORDER BY count(*) DESC) a
where rownum < 11
   ;

ACCEPT sids PROMPT 'Enter value for sids: '

select oc.sid, s.sql_text, s.piece, count(*)
from v$open_cursor oc, v$sqltext s
where oc.sid in (&sids)
and oc.address = s.address
and oc.hash_value = s.hash_value
group by oc.sid, s.sql_text,s.piece
having count(*) > 10
order by oc.sid, count(*), s.piece, s.sql_text
/
spool off

Return to Index of SQL Scripts

Home | Company Profile | Services | Contact Us | SQL scripts and tips | Quiz Legal