Monitoring and Managing 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:

o init.ora parameter open_cursors
o v$open_cursor view
o 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

'select count(*) from v$open_cursor' =>
implicit cursors used +
distinct explicit cursors opened +
dynamic cursors PARSED and NOT CLOSED.

- 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

'select value from v$sysstat where statistic# = 3' =>
implicit cursors used +
distinct explicit cursors opened +
dynamic cursors OPENED.

- 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 =
implicit cursors used +
distinct explicit cursors opened +
dynamic cursors OPENED.

- Accumulates dynamic cursors OPENED and NOT CLOSED over a session

================================================================

HTHU

Ankur Shah
Oracle DBA
DHR-GA


----- Original Message -----
From: "Rajni Sharma" <[Email Address Removed] "LazyDBA.com Discussion" <[Email Address Removed] Monday, May 21, 2001 3:31 AM
Subject: cursor opened by a specific user


hi,

how to find the no of cursors opened by a specific user and the curent
status of cursor.
What do status, curbound, curnull ,curfetch mean?

Regds,
Rajni


--------
Oracle documentation is here: http://tahiti.oracle.com/pls/tahiti/tahiti.homepage
To unsubscribe: send a blank email to oracledba-[Email Address Removed] subscribe: send a blank email to oracledba-[Email Address Removed] the list archive: http://www.LAZYDBA.com/odbareadmail.pl
Tell yer mates about http://www.farAwayJobs.com
By using this list you agree to these terms:http://www.lazydba.com/legal.html

Oracle LazyDBA home page