Re: shrinking undo tablespace

In article <aetrje$t95$1_at_lust.ihug.co.nz>, "Howard says...
>
>
>"sg" <s4v4g3_at_europe.com> wrote in message news:3D126E20.80707_at_europe.com...
>> I am aware of that command however I mean undo tablespace not temporary
>> tablespace,
>
>Huge apologies. I should learn to read more carefully.
>
>>for example if somehow my undo increases to a very big size
>> how can I reduce it? In RBS we could shrink manually then reduce
>> tablespace size but in 9i this seems very hard, or we have to wait SMON
>> wakes up and frees up extents or we have to drop the undo tablespace and
>> create a new one. Are there any more options?
>>
>
>Forget it. You are using automatic undo, so if the tablespace has grown,
>it's because Oracle thinks you need it that big (undo_retention etc etc). My
>only advice to students these days for tuning undo is: throw disk space at
>it. It's cheap, and you shouldn't care.
>
>The alternative is to have a quite small secondary undo tablespace. Change
>the database's undo tablespace to the new one. That will cause all the undo
>in the old, proper tablespace to become dead (eventually -there may still be
>live transactions using the old tablespace), at which point you should be
>able to shrink it, using the same resize datafile command. Once it's shrunk,
>switch back to using the proper tablespace.
>
>Be aware that doing this violates the entire point of automatic undo,
>including undo_retention. You render yourself liable to 1555s, and forget
>flashback.
>
>Anyway, the point is that a database *can* have more than one undo
>tablespace at a time, and by switching between them, you render the one
>switched away from liable to the sort of maintenance you are thinking of.
>
>And sorry for the stuff up about UNDO and TEMP again.
>
>Regards
>HJR
From what I understand, SMON will shrink the undo segments when necessary, and undo segments will be removed automatically when no longer needed as well. Now that doesn't address the tablespace size issue, but what it should mean is that you can manually shrink the file in the same way as normal tablespace files. Seems to work OK as you can see from this:

SQL> alter database datafile 'd:\oracle\oradata\ora92\undotbs01.dbf' resize 200M ;

Database altered.

Of course, as HJR points out, the undo tablespace size grows as Oracle needs it to, so you may end up causing yourself some grief by manually interfering with what is supposed to be an automatic process. There may be times when that is valid (say for example you know that you have a huge monthly update and small update amounts in between), but if you have the space for the huge monthly updates, then why worry about leaving the tablespace as big as is needed for the monthly processing?

>
>
>>
>> Howard J. Rogers wrote:
>>
>> > Assuming you are therefore using the proper 'tempfile' type of temporary
>> > tablespace, the command is:
>> >
>> > alter database tempfile 'D:\ORACLE\ORA92\DB9\TEMP01.DBF' resize 80m;
>> >
>> > (Pick a size to suite. Normal rules apply: if getting to the new size
>would
>> > result in real data being chopped off, it won't work).
>> >
>> > Regards
>> > HJR
>> >
>> > "sg" <s4v4g3_at_europe.com> wrote in message
>> > news:3D1256D3.1050707_at_europe.com...
>> >
>> >>sorry forgot the version number..... it's 9.2
>> >>
>> >>Howard J. Rogers wrote:
>> >>
>> >>
>> >>>Version?
>> >>>
>> >>>HJR
>> >>>
>> >>>"sg" <s4v4g3_at_europe.com> wrote in message
>> >>>
>> > news:3D125536.40305_at_europe.com...
>> >
>> >>>>Hi
>> >>>>
>> >>>>Does anyone know how to shrink an undo tablespace? So far the only way
>I
>> >>>>can find is by create a new one and drop the old one!
>> >>>>
>> >>>>Any other way?
>> >>>>
>> >>>>
>> >>>>
>> >>>
>> >
>> >
>>
>
>

HTH. Additions and corrections welcome.

Pete

SELECT standard_disclaimer, witty_remark FROM company_requirements; Received on Thu Jun 20 2002 - 21:54:38 CDT