Re: make UNDO tablespace smaller

A restart will not clear up the undo segments, it will simply keep them offline, but they will take up space. This enables them to be used if there is a new transaction and all of the online undo segments are in use. Eventually, SMON will deallocate expired extents from the undo segments, but this process runs at a 12 hour interval. Current transactions will also reuse extents from other segments when space is needed.

One option I suggest is waiting for SMON to deallocate extents. If your retention_time is set to a large value, this may take quite a while. This particular issue was one that we discussed recently at IOUG. The question is how SMON deallocates the extents. I suspect that the extents will be deallocated in a way as to create holes in the tablespace. This is currently being tested and is just theoretical at this time.

The other option is to create a new undo tablespace and switch over to that one. You do not need to restart the instance. However, once the old tablespace is taken offline pending (this is automatic when the new tablespace is brought online), you run the risk of ORA-1555 errors. All current transactions will continue to use the old tablespace and all new transactions will bind to segments in the new tablespace. Once all current transactions are complete, the tablespace is taken completely offline. Any segments in that tablespace are now unavailable. If a query needs the undo in one of these segments, it will terminate with the Snapshot Too Old error.

-- 
Daniel W. Fink
http://www.optimaldba.com



Connor McDonald wrote:



>If this is an auto-undo tspace, I think you're either

>up for a bounce (to clear down the undo segs) or

>creating a new undo tspace and switching over to that

>one.

>

>others may have a better solution.  Enhancement

>2679883 is for manually shrinking an undo tspace

>

>hth

>connor

>

> --- Jack van Zanen <JACK_at_QUANTSYSTEMS.NL> wrote: > Hi

>List

>  

>

>>During a large migration (imp/exp) our undo

>>tablespace grew quite large and

>>now I need to make it smaller. Documentation I found

>>only refers to making

>>bigger and not smaller. Resize command tells me ts

>>contains segments beyond

>>the size I want to resize it to. 

>>Do I have to ofline all undo segments first or

>>..........?

>>

>>TIA

>>

>>Jack

>>    

>>




-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Daniel W. Fink
  INET: optimaldba_at_yahoo.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).