o
Exception
java.sql.SQLException:
ORA-00604: error occurred at recursive SQL level 1
ORA-01000:
maximum open cursors exceeded ORA-00604: error occurred at recursive SQL level
1
o Introduction
For every SQL statement execution in Oracle Database, certain
area in the memory is allocated. Oracle PL/SQL allows you to name this area.
This private SQL area is called context area or cursor. These cursors take up
space in the shared pool, which is essential memory component of Oracle
Database, specifically in the library cache. To keep a renegade session from
filling up the library cache or clogging the CPU with millions of parse
requests, the OPEN_CURSORS database parameter
must be set to limit the cursors.
The OPEN_CURSORS parameter sets the
maximum number of cursors that each session can have open, per session. For
example, if the value of OPEN_CURSORS is set to 1000, then
each session can have up to 1000 cursors open at one time.
o Troubleshooting
1.
Get the session id having high number
of open cursors count using following SQL.
SELECT s.username,
s.sid,
s.serial#,
a.VALUE open_cursor_count
FROM v$sesstat a, v$statname b, v$session s
WHERE a.statistic# = b.statistic# AND s.sid =
a.sid AND b.name = 'opened cursors current' AND s.username IS NOT NULL
ORDER
BY a.VALUE DESC;
2.
Get the SQL which opens most cursors
for the top session in above query.
SELECT a.sql_id, b.sql_fulltext, a.open_cursors
FROM (
SELECT sql_id, COUNT (1) open_cursors
FROM v$open_cursor
WHERE sid = 913
GROUP BY sql_id) a,
gv$sqlarea b
WHERE a.sql_id = b.sql_id
ORDER
BY a.open_cursors DESC;
We will get the top queries which are opened but not
closed. Usually implicit cursors are closed immediately by Oracle itself. But
the cursors which are opened explicitly through PL/SQL or java Statement object
should be closed by program itself. So we need to verify that we are closing
the SQL statements from the above query output properly.
3. Also monitor the current higher value and
configured value through following query.
SELECT
MAX (a.VALUE) AS highest_open_cur, p.VALUE AS max_open_cur
FROM v$sesstat a, v$statname b, v$parameter
p
WHERE a.statistic# = b.statistic# AND b.name
= 'opened cursors current' AND p.name = 'open_cursors'
GROUP
BY p.VALUE;
If the configured value is not
adequate then increase the open_cursor_count with following SQL statement.
ALTER
SYSTEM SET open_cursors=1500;
