SQL Script TOP Cursors

Updated: 08-Jan-2001
Version: 2.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

REM Copyright (C) Think Forward.com 1998- 2005. All rights reserved. 

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

Related Links

Oracle 8: (SQL Script) TOP Cursors

Return to Index of SQL Scripts

Site Map

General Information : Home | Company Profile | Contact Us | Legal
Database Support : Proactive Support | Pay Per Incident Support
Expert Services : High Availability | Creative Solutions | Training
Build Services : Oracle Installations | Revive your Oracle Setup | Low Cost Development
Free Oracle Resources: DBA Scripts | Oracle Links