Troubleshootong open cursor issue in oracle

posted Jun 6, 2011, 6:30 AM by Sachchida Ojha   [ updated Jun 6, 2011, 6:44 AM ]
Display the open cursor setting in the database
SQL>show parameter open

SQL> select user_name,count(*)  from v$open_cursor group by user_name;

SQL>select * from v$sysstat where name like '%opened cursors current%';

TOP SQL  opening the CURSOR

 select user_name,SQL_TEXT,count(*) as cnt  from  v$open_cursor  where user_name='DBAREF' group by user_name,SQL_TEXT order by  cnt desc;

Another SQL to capture current open cursors by users

SQL> select s.username, max(a.value)
from v$sesstat a, v$statname b, v$session s
where a.statistic# = b.statistic#
and s.sid (+)= a.sid
and = 'opened cursors current'
group by s.username;

select * from v$sysstat where name like '%opened cursors%';