Oracle open cursors

OPEN_CURSORS specifies the maximum number of open cursors (handles to private SQL areas) a session can have at once

--For current session
select a.value, b.name 
            from v$mystat a, v$statname b 
           where a.statistic# = b.statistic# 
             and b.name = 'opened cursors current', for current session

--For all sessions
select a.sid, a.value, b.name
      from v$sesstat a, v$statname b
     where a.statistic# = b.statistic#
       and b.name = 'opened cursors current'

select a.sid, a.saddr, a.user_name, b.program, count(*)
from v$open_cursor a, v$session b
where a.sid = b.sid
group by a.sid, a.saddr, a.user_name, b.program
order by count(*) 

Hongfeng Sun : 10 Nov 2006 14:27 |  Tags: Oracle |  Comments (0) |  Report a problem