In this Document
  Purpose
  Scope and Application
  Monitoring Open Cursors & Troubleshooting ORA-1000 Errors
  References

---

Applies to:

PL/SQL - Version: 10.2.0.4 and later   [Release: 10.2 and later ]

Purpose

This article contains information on how different cursors are managed and monitored in PL/SQL.

Scope and Application

It addresses issues with the open_cursors parameter, as well as the V$OPEN_CURSOR view in the context of implicit, declared, and dynamic cursors.

Monitoring Open Cursors & Troubleshooting ORA-1000 Errors

This section covers the monitoring and managing of the open cursors.

The number of open cursors can limit operation in PL/SQL procedures and SQL*Plus sessions. While the parameter open_cursors sets the limit, programming issues can cause the following error:

ORA-1000 maximum open cursors exceeded

Three important values are the following:  

1. init.ora parameter open_cursors
2. V$OPEN_CURSOR view
3. V$SYSSTAT view
   

These values are similar, but differ in their accounting of Dynamic Cursors.

Note: Dynamic cursors are those opened using DBMS_SQL.OPEN_CURSOR().

The means to compute those values are as follows: 

View v$open_cursor

The following SQL calculates the:

implicit cursors used + distinct explicit cursors opened + dynamic cursors PARSED and NOT CLOSED.

It also:
- Accumulates dynamic cursors PARSED and NOT CLOSED over a session
- Available to system/manager
- Includes the text of open cursors - helpful for debugging
- Since this view does not track unparsed (but opened) dynamic cursors,
the count(*) may not show all cursors that count against open_cursors.

View v$sysstat

The following SQL calculates the:

implicit cursors used + distinct explicit cursors opened + dynamic cursors OPENED.

It also:
- Accumulates dynamic cursors OPENED and NOT CLOSED over a session
- Available to system/manager
- Since this view does track unparsed (but opened) dynamic cursors, the statistic#3 shows all cursors that count against open_cursors.

init.ora parameter open_cursors

This parameter equal:
implicit cursors used + distinct explicit cursors opened + dynamic cursors OPENED.

It also:
- Accumulates dynamic cursors OPENED and NOT CLOSED over a session

ORA-1000

The following are several items to check when encountering ORA-1000 in PL/SQL:

1. Be sure that all dbms_sql cursors opened at DECLARE time are closed. Every unclosed OPEN counts against open_cursors. The number of open cursors can be determined as follows in SQL*Plus:

2. Be aware that v$open_cursor only tracks the CUMULATIVE number of implicit + distinct explicit cursors in the procedure PLUS unclosed dynamic cursors that have been PARSED in the session.

Note: It does not include any dynamic cursors that were opened but not parsed.

The text of the parsed, open cursors can be determined as follows in SQL*Plus:

3. Dynamic cursors persist from run-to-run in a session, but are not closeable after a procedure has completed. This can accumulate and error-out with open_cursors after a number of runs. They will not
appear in v$open_cursors after a session.

The following are two code snippets that can help diagnose ORA-1000. Text lines are shown for each cursor.

-- snippet 1

-- snippet 2

References

NOTE:1012266.6 - Overview of ORA-1000 Maximum Number of Cursors Exceeded

Troubleshooting Details

Diagnostic Queries

The following SQL queries are useful in diagnosing the ORA-01000 problem. To execute these queries, you need to logged into the database as an administrator or your DB Admin grants you permissions to select from those v$ views.

1. Check OPEN_CURSORS parameter value in your database.
   Oracle uses the initialization parameter OPEN_CURSORS in init.ora to specify the maximum number of cursors a session can have at once. The default value is 50. Unfortunately, this default value is usually too small for systems such as WebLogic Server. To find out the value of OPEN_CURSORS parameter in your database, you may use the following query:
   
   SQL> show parameter open_cursors;
   
   NAME                                 TYPE        VALUE
   ------------------------------------ ----------- ------------------------------
   open_cursors                         integer     1000
   It is important to set the value of OPEN_CURSORS high enough to prevent your application from running out of open cursors. The number will vary from one application to another. Assuming that a session does not open the number of cursors specified by OPEN_CURSORS, there is no added overhead to setting this value higher than actually needed.
2. Get number of open cursors.
   The query below shows number of open cursors for each session opened by user 'SCOTT' in descending order.
   
   SQL> select o.sid, osuser, machine, count(*) num_curs
   
     2  from v$open_cursor o, v$session s
     3  where user_name = 'SCOTT' and o.sid=s.sid
     4  group by o.sid, osuser, machine
     5 order by  num_curs desc;
          SID OSUSER               MACHINE                                              NUM_CURS
   ---------- ---------------- ------------------------------------------------- ----------
          217                                m1                                                           1000
           96                                 m2                                                            10
          411                                m3                                                             10
           50                                test                                                              9
   When you use connection pool in WebLogic Server, the user_name in this query should be the user_name you use to create the connection pool, assuming that the connection is retrieved from a connection pool. The query result also gives machine names. From the query result, identify SIDs with high number of open cursors and machine names on which you run your WebLogic Servers.
   
   Please note that v$open_cursor can track dynamic cursors (cursors opened using dbms_sql.open_cursor()) PARSED and NOT CLOSED over a session. It doesn't track unparsed (but opened) dynamic cursors. Using dynamic cursors is not common in application. This pattern assumes that dynamic cursors are not used.
3. Get the SQL being executed for the cursors.
   Take the SID identified from the above query result and run the following query:
   
   SQL> select q.sql_text
     2  from v$open_cursor o, v$sql q
     3  where q.hash_value=o.hash_value and o.sid = 217;
   
   SQL_TEXT
   ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
   select * from empdemo where empid='212'
   select * from empdemo where empid='321'
   select * from empdemo where empid='947'
   select * from empdemo where empid='527'
   ...
   The result shows what queries are being executed on the connection. It gives you a starting point to trace back to see where is the source of open cursors.