CURSORS
A cursor is a temporary work area created in the system memory when a SQL statement is executed. It can hold more than one row but can process only one row at a time. A set of rows the cursor holds is called an active set. The cursor might be used for retrieving data on a row-by-row basis like a looping statement.
FIND WHICH SESSION IN USING MORE CURSORS.
col user_name for a20
SELECT
sid,user_name, COUNT(*) “Cursors per session”
FROM v$open_cursor where user_name not like ‘SYS’
GROUP BY sid,user_name;
Find which SQL is using more cursors
select sid,sql_id ,sql_text, count(*) as “OPEN CURSORS”, USER_NAME from v$open_cursor where sid in (‘&SID’) GROUP BY SID,SQL_TEXT,USER_NAME,sql_id;
Solution to overcome the issue
Increase the open_cursors count limit in Parameter File
alter system set open_cursors = 500 scope=both;
Or
TEMPORARY SOLUTION
Find the session which is using more cursors and kill the session
Alter system kill session ‘SID,SERIAL#’ immediate;
