Rules for Closing Cursors

David Clement

September 2004

Oracle versions 7-8

Oracle cursors are constructed during hard parsing. They are memory structures in the cursor cache of the shared pool in the SGA. They include references to the SQL statements associated with them and the data most recently fetched, which is stored in the PGA, and a context area that provides other information. To close a cursor is to release the memory, which among other things loses the connection between the cursor and its SQL statement. A cursor re-opened after closing will have to have its SQL statement hard-parsed again, just as if the statement had never been submitted.

It is also true that a cursor allocated for a user process will not necessarily be cleaned up automatically, because the rules for cursor garbage collection are subtle and change with different Oracle versions. Cursor garbage collection may not happen if the routine that allocated the cursor was not top-level, because Oracle waits for the top-level routine to exit before rescuing cursor cache space allocated at lower levels in the call stack. Garbage collection probably won't happen if the user process has terminated unexpectedly without disconnecting from the Oracle instance, because in that case the instance does not know that it can clean up the cursor. Garbage collection is supposed to happen for a normal disconnection, but not all database-connection drivers have been cleanly coded. So a program that opens cursors and exits without closing them may cause a "cursor leak," in which unused cursors remain open indefinitely. A cursor leak raises the risk of incurring ORA-01000, "maximum open cursors exceeded."

It is important to keep cursors open for as long as they are used, without opening and closing them repeatedly in a loop. It's also important to close them as soon as the program logic is done with them.