Skip to Main Content
  • Questions

  • ORA-01000 - How to close open CURSORs?

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Mohammed Amine.

Asked: August 02, 2016 - 11:26 pm UTC

Last updated: August 04, 2016 - 9:55 am UTC

Version: 12.1

Viewed 10K+ times! This question is

You Asked

Hi,

I have a cursor leak at the application level (Java), how can I close open cursors?

If I Kill the session, does this close the related cursors?
Also, if I use DBMS_SHARED_POOL.PURGE to flush the query behind the issue, does this close the open cursors?

Best regards,
Amine

and Chris said...

If you have a cursors leak in your application, you really need to analyze your code to ensure they're closing them correctly!

I can't really comment on how you'd do that. This SO thread has some advice which may help:

http://stackoverflow.com/a/12246631/1485955

Yes, closing/killing a session will close any related cursors.

Don't use DBMS_SHARED_POOL.PURGE! This will flush the query from the shared pool. So you'll need a hard parse next time you execute the statement.

Presumably your application will run the same SQL many times across different sessions. If this is the case, regularly purging statements will create a parsing problem for you.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thanks

Mohammed Amine HLIMI, August 05, 2016 - 9:09 pm UTC

Thanks for the feedback.

I know, using DBMS_SHARED_POOL.PURGE will force a Hard parse the next time the query is executed, but I wanted the quickest way to close all open CURSORs related to some problematic query (from another session) without killing the session. But it seems impossible: "PGA-Private SQL area" <> "SGA-Shared Pool-Shared SQL Area".

Of course killing the session will close all related open cursors, it just took a little time before it reflects in my query of v$sesstat ... I wanted to edit the question to remove this part but couldn't :)

Anyway thank you again for the answer.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

DBMS_SHARED_POOL

More on PL/SQL routine DBMS_SHARED_POOL here