Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Mansi.

Asked: July 03, 2018 - 5:14 am UTC

Last updated: July 05, 2018 - 8:08 am UTC

Version: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

Viewed 1000+ times

You Asked

Hi Team,

I went through some weird situation where I am not able to figure out the reason behind error
ORA-01652 unable to extend temp.

While executing a PL/SQL block at one point I am facing this issue. ORA-01652 unable to extend temp.

While checking currently running query, I found one Insert from select is causing this issue, I have tried extending TEMPFile as well but same issue.

After that,I just ran select query from PL/SQL block in SQL developer and it keep running, I can see there were multiple tochar and lpad function used in select.

I just removed all and run again, it worked.

After that when I ran the PL/SQL block,that also ran well.

Any suggestion why this weird thing happens.

and Connor said...

Good information here...

https://asktom.oracle.com/pls/apex/asktom.search?tag=ora-01652-unable-to-extend-temp

https://asktom.oracle.com/pls/asktom/asktom.search?tag=ora-01652-unable-to-extend-temp-segment-by-2048-in-table-space-temp

But put simply - you need "n" megabytes of temporary storage for an operation, and there was "n" available. Most likely causes are:

- its a global temporary table you are inserting into
- you are doing large sorts
- you are doing large joins (hash join etc)
- you are doing large aggregations (group by, distinct)

All of these *start* with using RAM and will spill over to disk when RAM allocations are exhausted.

Is this answer out of date? If it is, please let us know via a Comment
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

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database