Re: shrinking undo tablespace

From: sg <s4v4g3_at_europe.com>
Date: Fri, 21 Jun 2002 08:07:04 +0200
Message-ID: <3D12C288.20701@europe.com>

I came up with this question because the other day I was creating a new instance for a small development so my disk space was limited then I noticed the undo was assigned 500MB space! I thought hell in 8i the database assistant creates 525MB by default and it seems this happen in 9i too but in 8i I always manually resized so I had more space for data. which was not possible with that new 9i instance I was creating, I didnt know how to reduce number of RBS to make it consumes less space! My undo retention is set at 300, 5 minutes

Regarding your suggestions, Howard you said we could have mire than one undo at time, how is this possible. In the ILT courses the guides states that a instance can only have one undo tablespace I was not very convinced so I actually tried to put undo_tablespace="undo1, undo2" which does not work.

Peter again from ILT guides it states that smon wakes up every 12 hours in normal activities. I guess when you say "SMON will shrink the undo segments when necessary" you probably mean when there are more activities?

Sometimes we may also run into situations where the undo might eats up a whole filesystem..... after high activities but after these activities ceases there is only one transaction going but the undo is still big in this case we would have to wait 12 hours for SMON to wakeup? Or we could wakeup SMON with oradebug to do his job?

Pete Sharman wrote:

> 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 Fri Jun 21 2002 - 01:07:04 CDT