Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question.

Asked: March 15, 2012 - 11:57 am UTC

Last updated: March 19, 2012 - 10:12 am UTC

Version: 11.2.0.2

Viewed 10K+ times! This question is

You Asked

This is regarding Undo tablespace and temp tablespace management.
My understanding is space in both get re-used. Undo tablespace (automatic undo management) gets cleaned up when contents get expired and flashback limits are guaranteed. Temp gets full but gets reused.

Please advise how you would like to cleanup these tablespace manually if required to do urgently (imagining data in them is no longer required).



and Tom said...

There is no case I can think of that would require you, or even motivate you to "clean them up". They are always "clean"


If you want to "make them smaller" (which is a way to self inflict performance problems since they will get bigger again and that takes work), you can:

a) create a new smaller version of them
b) alter the database/system to use the new temporary/undo tablespace
c) wait a bit for all activity to be done
d) drop the old big ones.

Usually, this is a big waste of time as they get big again real soon.

Rating

  (2 ratings)

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

Comments

Thanks

A reader, March 16, 2012 - 1:31 pm UTC

We frequently face errors related to undo not being able to extend (undo full).So, do you advise to add datafiles to undo tablespace ?

Do you feel temp tablespace be managed by adding tempfiles ?

Thanks Tom.
Tom Kyte
March 16, 2012 - 4:11 pm UTC

My advise would be to size the undo tablespace to fit your apparent needs, yes. One way is to add datafiles, another is to allow the existing files to autoextend, another is to resize the existing files.

But that doesn't have anything to do with your original question?


If you are exceeding your temporary tablespace allocation, you either

a) decrease your need of temp - run fewer queries concurrently, tune queries to not use temp, increase PGA memory available to processes needing to sort

b) increase your temp tablespace allocation

New in 11.2 Shrink temp

David P, March 18, 2012 - 11:38 pm UTC

Last week I learnt that Oracle 11.1 introduced "ALTER TABLESPACE temp SHRINK SPACE KEEP 100M;" which allows you to shrink a temporary tablespace without the effort of creating another and switching.

I agree with Tom that it's almost useless, but it exists. (In our case, we were on a Windows Virtual machine that wouldn't extend its disk, and temp had grown during data load and wouldnt be needed later. Of course reloading the dataload will make it grow again. It was a desparation measure.)
Tom Kyte
March 19, 2012 - 10:12 am UTC

learnt something new today. thanks!
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.