Home » RDBMS Server » Server Administration » UNDO tablespace: how to shrink datafiles ? (Oracle 9i, AIX)
UNDO tablespace: how to shrink datafiles ? [message #347221] Thu, 11 September 2008 02:06 Go to next message
psoftneto
Messages: 44
Registered: July 2008
Member
Hi all,

Please can some one tell me how to shrink UNDO tablespace datafiles ?

Even after shutdown/startup the datafiles still have the same percentage of use.

Will those datafiles shrink automatically when user will make a new request ?

Thanks for your response.
Re: UNDO tablespace: how to shrink datafiles ? [message #347231 is a reply to message #347221] Thu, 11 September 2008 02:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Will those datafiles shrink automatically when user will make a new request ?

No. If files are in autoextend mode then do not automatically shrink.

You have to create a new undo tablespace, set it as the active one and drop the old one.

Regards
Michel
Re: UNDO tablespace: how to shrink datafiles ? [message #347234 is a reply to message #347231] Thu, 11 September 2008 02:47 Go to previous messageGo to next message
psoftneto
Messages: 44
Registered: July 2008
Member
Hi Michel,

Does than mean every time the tablespace is full - we have to create a new tablespace ?

Thanks.
Re: UNDO tablespace: how to shrink datafiles ? [message #347235 is a reply to message #347234] Thu, 11 September 2008 02:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No, undo space is reused but files don't shrink.
In the normal state, undo tablespace should be always "full" (occupied by extents/segments), if it is not then it is oversized and you're wasting disk space.

Regards
Michel
Re: UNDO tablespace: how to shrink datafiles ? [message #347433 is a reply to message #347235] Thu, 11 September 2008 11:34 Go to previous messageGo to next message
macdba
Messages: 27
Registered: May 2005
Location: US
Junior Member
UNDO TS remains occupied till undo_retention. When new transaction requires a space, transaction which are expired are removed from undo segments and space is made is available. By default Oracle does not guarantee undo retention.

In 10G there is parameter undo_retention_gurantee this one guarntees undo retention. So check if it is set. If there is no space available in UNDO and old segments are not expired, new transactions will abort.

You dont need to shrink the UNDO segments like the old rollback segments if the undo management is AUTO

regards
--Mak
Re: UNDO tablespace: how to shrink datafiles ? [message #347753 is a reply to message #347221] Sat, 13 September 2008 13:01 Go to previous messageGo to next message
rljohnsn
Messages: 1
Registered: September 2008
Junior Member
psoftneto wrote on Thu, 11 September 2008 02:06
Hi all,

Please can some one tell me how to shrink UNDO tablespace datafiles ?

Even after shutdown/startup the datafiles still have the same percentage of use.

Will those datafiles shrink automatically when user will make a new request ?

Thanks for your response.



During your next maintenance window give this script a try:

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:153612348067

The script determines if any data files can be re-sized smaller and if so generates the sql to do so.
Re: UNDO tablespace: how to shrink datafiles ? [message #347756 is a reply to message #347753] Sat, 13 September 2008 13:21 Go to previous message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This script is for data files and not for undo tablespace.

Regards
Michel
Previous Topic: a 12 gb ram database server
Next Topic: oracle database Migration from 7.1.0 version to 10.2.0
Goto Forum:
  


Current Time: Fri Dec 09 19:45:02 CST 2016

Total time taken to generate the page: 0.09082 seconds