OraFAQ Forum: Server Administration » UNDO tablespace: how to shrink datafiles ?

Home » RDBMS Server » Server Administration » UNDO tablespace: how to shrink datafiles ? (Oracle 9i, AIX)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

UNDO tablespace: how to shrink datafiles ? [message #347221]

Thu, 11 September 2008 02:06

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.

Report message to a moderator

Re: UNDO tablespace: how to shrink datafiles ? [message #347231 is a reply to message #347221]

Thu, 11 September 2008 02:37

Michel Cadot
Messages: 68666
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

Report message to a moderator

Re: UNDO tablespace: how to shrink datafiles ? [message #347234 is a reply to message #347231]

Thu, 11 September 2008 02:47

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.

Report message to a moderator

Re: UNDO tablespace: how to shrink datafiles ? [message #347235 is a reply to message #347234]

Thu, 11 September 2008 02:50

Michel Cadot
Messages: 68666
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

Report message to a moderator

Re: UNDO tablespace: how to shrink datafiles ? [message #347433 is a reply to message #347235]

Thu, 11 September 2008 11:34

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

Report message to a moderator

Re: UNDO tablespace: how to shrink datafiles ? [message #347753 is a reply to message #347221]

Sat, 13 September 2008 13:01

rljohnsn
Messages: 1
Registered: September 2008

Junior Member

psoftneto wrote on Thu, 11 September 2008 02:06

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.

Report message to a moderator

Re: UNDO tablespace: how to shrink datafiles ? [message #347756 is a reply to message #347753]

Sat, 13 September 2008 13:21

Michel Cadot
Messages: 68666
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

This script is for data files and not for undo tablespace.

Regards
Michel

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Jul 08 00:33:51 CDT 2024