Home » SQL & PL/SQL » SQL & PL/SQL » Closed Cursor query in procedure - listed in v$open_cursor
Closed Cursor query in procedure - listed in v$open_cursor [message #37157] Mon, 21 January 2002 19:07 Go to next message
Messages: 17
Registered: June 2001
Junior Member
I have problem in cursors. After executing the procedure i am getting the query used in the cursor getting listed in v$open_cursor table. But i have closed the cursor in the procedure. I tried with 'FOR <cursor_name> IN <query>' also. SQL_TEXT field V$open_cursor include the cursor query. How to solve this. Even if i give "select 'x' from dual" in the cursor that is getting listed in v$open_cursor. I could n't find out the reason. Please update me regarding this.

Thanks and regards,
Re: Closed Cursor query in procedure - listed in v$open_cursor [message #37165 is a reply to message #37157] Tue, 22 January 2002 00:02 Go to previous messageGo to next message
pratap kumar tripathy
Messages: 660
Registered: January 2002
Senior Member

Do not worry about it, it is not unusual behaviour.

The cursors listed in V$OPEN_CURSORS are, in fact, still open. This is a performance optimization method used by the server. Even though you have CLOSED the cursors, they are still cached in Server memory.

This is not a bug. The code leaves cursors open and caches them, as intended. The CLOSE_CACHED_OPEN_CURSORS parameter in init.ora forces all of these cached cursors to be closed at the end of each transaction, if that is desired.

The server caches cursors opened by the PL/SQL engine. Each time a cursor is closed, it is really moved to an Least Recently Used (LRU) list of open cursors and left open. This is done as a
performance improvement by saving a 'round_trip' from Client to Server each time a cursor is opened.

Hope it helps!!!

Re: Closed Cursor query in procedure - listed in v$open_cursor [message #37932 is a reply to message #37165] Thu, 07 March 2002 05:49 Go to previous message
Gaetano Giunta
Messages: 1
Registered: March 2002
Junior Member
How does this interact with the SESSION_CACHED_CURSORS parameter? In my case the number of cursor closed by PL/SQL stored procedures but kept open by the DB seem to grow without an end, even though SESSION_CACHED_CURSORS is set to 0 (I have PRO*C apps that run a never-ending session). Will eventually cached cursors fill up the SGA and put the db to a crawl?
Previous Topic: Update Statement Error
Next Topic: Line number in SQL
Goto Forum:

Current Time: Wed Oct 27 04:04:49 CDT 2021