SQL Script TOP Cursors

Updated: 03-Oct-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

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 sid, user_name ,count(*)
    FROM v$open_cursor
    GROUP BY sid, user_name
   ORDER BY count(*)
   ;

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

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