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.
Dig deeper on Oracle database design and architecture
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.