Q

When did Oracle begin implicitly closing cursors?

In your response regarding cursor memory leaks, you said: "It used to be that if you did not close a cursor, that memory stayed allocated and unusable to anything else. This is no longer the case with Oracle." Do you know with what version Oracle started implicitly closing cursors at the end of PL/SQL block execution? Also, when this happens implicitly, does this mean that your cursor is no longer contributing to the max_open_cursor count?

In your response regarding cursor memory leaks, you said: "It used to be that if you did not close a cursor, that

memory stayed allocated and unusable to anything else. This is no longer the case with Oracle. If you open a cursor and forget to close it, Oracle will close the cursor for you when the PL/SQL block ends execution. This assumes your PL/SQL block does reach an end."

Do you know with what version Oracle started implicitly closing cursors at the end of PL/SQL block execution? Also, when this happens implicitly, does this mean that your cursor is no longer contributing to the max_open_cursor count, meaning if there are 49 open cursors (and yours is one of them) when Oracle implicitly closes your cursor there will be 48 open cursors? Thanks so much for your help.

I do not recall the exact version in which Oracle implicitly started closing cursors. But I know for sure that this happens in Oracle 8i and higher. I do not recall having memory leaks in Oracle as far back as early Oracle 7 days, but I am not as sure of that. Hopefully, you are using a supported version of Oracle which means your cursors are being closed implicitly.

Once a cursor is closed, it no longer contributes to the max open cursor count. Keep in mind that there are other cursors aside from your explicit CURSOR definition in a PL/SQL block. A simple SELECT statement opens an implicit cursor in the Oracle database, which does count against the max cursor count. This implicit cursor will be closed automatically for you once the SELECT statement is done being processed.

This was first published in March 2006

Dig deeper on Oracle database design and architecture

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close