Skip to Main Content
  • Questions

  • Will open cursor hold up more tablespace when it is not closed?

Breadcrumb

XMAS_TREE

The AskTOM team is taking a break over the holiday season, so we're not taking questions or responding to comments. Please have a happy and safe festive season and as always, thanks for being a member of the AskTOM community.

Question and Answer

Connor McDonald

Thanks for the question, kumaresan.

Asked: July 17, 2016 - 9:17 pm UTC

Last updated: July 18, 2016 - 12:40 am UTC

Version: 11g

Viewed 1000+ times

You Asked

Hi Tom,

I am using oracle 11g and the tablespaces keeps growing, I have recently identified a issue with open cursors which is not closed when the session was closed.

Will open cursor eats up all the spaces which leads to consume more temp spaces?

those cursors are fetching good amount of data from tables and the sessions are being re-used in connection which keeps the session active for many hours.

Please help.


and Connor said...

It is *unlikely* that an open cursor will use up disk space. However, if that cursor required (say) a large sort segment from which the results are now being read from, then that segment will remain until the results have been read (ie, fetching is finished).

If you have sessions that read some of the results, and then ever finish fetching, then this might have an adverse impact on temporary tablespace consumption.

But generally, for apps that dont close cursors, you normally start seeing problem with the number of open cursors exceeding allowed limits before you run out of temp space.

Rating

  (1 rating)

We're not taking comments currently, so please try again later if you want to add a comment.

Comments

kumaresan ps, July 18, 2016 - 10:47 am UTC

Thanks Connor.
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 channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.