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);
4 comments:
Hi. Need help on the SQL you posted to track too many open cursors. I am not a DBA, I am just a developer. I am getting this error in one of my projects. I need your help.
1. When do I run the SQL you posted? Do I run it after getting the open cursor error?
2. What am I looking for in the result? What would help me identify what SQL was causing the problem?
Sorry if the questions are too basic. I need help to resolve my too many cursor error.
Thanks,
WC
Hi,
You need to run it regularly, with setting proper value for threshold, you can capture all SQL statement and session which causes this issue.
For the result, you could look into session and SQL_text and find the offending sessions and fix the code.
If you need more help, please send the result of query to me.
Also, if you are using sqlplus, please login to database and run the following and send me the result.
show parameter cursor
Hope this helps.
Thanks Shevin.
I will try your recommendation.
Hi,
How know of much memory in RAM theses opened Cursor Take ?
THanks,
EFR
Post a Comment