Tuesday, May 26, 2009

Track many open cursors

If cursor in PL/SQL code get open but never get close, Oracle may report too many open cursors error in Alert log.
I found the following code handy to track the cause of problem.

select
SADDR,
SID,
USER_NAME,
ADDRESS,
HASH_VALUE,
SQL_ID,
SQL_TEXT
from
v$open_cursor
where
sid in(SELECT sid FROM V$OPEN_CURSOR group by sid having count(*)>&threshold);

No comments:

Post a Comment