Database Error Messages

This error is raised when a database session has tried to open more cursors than is allowed by the OPEN_CURSORS database initialization parameter.

There are two root causes for this error:

• Your workload requires a certain number of concurrently open cursors per session and the value of OPEN_CURSORS is too low to accommodate the workload. In this case, the remedy is to increase the value of the OPEN_CURSORS database initialization parameter.

• An application opens too many cursors and either does not close them anymore or does not close them as soon as they are no longer required. When an application opens cursors and never closes them, it is referred to as a cursor leak within the application.

To further diagnose the situation, open a new database connection as an administrative user and run the following SQL statements.

Check for cursor leaks

The following statement outputs any session that has hit the OPEN_CURSORS limit:

SELECT s.username, s.sid, s.serial#, ss.value AS open_cursors
FROM v$sesstat ss
INNER JOIN v$statname sn ON (ss.statistic#=sn.statistic#)
INNER JOIN v$session s ON (ss.sid=s.sid)
WHERE sn.name = 'opened cursors current'
AND ss.value = (SELECT p.value
FROM V$PARAMETER p WHERE name = 'open_cursors')
AND s.username IS NOT NULL;

Retrieve open cursor count for all sessions

The following statement lists the number of open cursors for all currently connected sessions:

SELECT sid, user_name, COUNT(*) AS open_cursors
FROM V$OPEN_CURSOR
WHERE user_name IS NOT NULL
GROUP BY sid, user_name
ORDER BY COUNT(*);

Retrieve open cursors and SQL text for a connected user

The following statement returns all sessions, their open cursors, and the corresponding SQL statements for a connected user.

Note: Replace <session_user_name> with the actual user name.

SELECT sid, user_name, sql_text, COUNT(*) AS open_cursors
FROM V$OPEN_CURSOR
WHERE user_name = UPPER('<session_user_name>')
GROUP BY sid, user_name, sql_text
ORDER BY sid, COUNT(*);

Retrieve open cursors and SQL text for a session

The following statement returns all open cursors and the corresponding SQL statements for a given session.

Note: Replace <session_sid> with the actual SID of the session.

SELECT sid, user_name, sql_text, COUNT(*) AS "OPEN CURSORS"
FROM V$OPEN_CURSOR
WHERE sid = <session_sid>
GROUP BY sid, user_name, sql_text
ORDER BY sid, COUNT(*);

Monitor open cursor count per session

The following statement lists all sessions, their highest open cursors count, and the currently allowed maximum number of open cursors according to the OPEN_CURSORS initialization parameter.

Use this SQL statement to monitor and determine how many cursors your application needs for normal operations:

SELECT s.sid, s.username, MAX(ss.value)
AS highest_open_cursors, p.value AS max_open_cursors
FROM V$SESSION s, V$SESSTAT ss, V$STATNAME sn,
V$PARAMETER p
WHERE ss.statistic# = sn.statistic#
AND s.username IS NOT NULL
AND ss.sid = s.sid
AND sn.name = 'opened cursors current'
AND p.name = 'open_cursors'
GROUP BY s.sid, s.username, p.value
ORDER BY s.username, MAX(ss.value);