OCI-01000
Cause
An application is attempting to open too many cursors within the current session. This could be a result of the application not closing cursors properly or keeping cursors open for longer than needed. This could also be a result of the application genuinely requiring more cursors than currently permitted by the OPEN_CURSORS parameter value. This could also be caused by cursors not being closed properly.
Action
Check the application code to see if currently opened cursors can be closed before opening new ones. If the application does require more cursors to be simultaneously open within a session, then increase the value of the OPEN_CURSORS parameter. If a potential leaked SQL_ID is identified in the error text, then it needs to be reviewed by an application developer.
Additional Information
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 theOPEN_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);