Re: shrinking undo tablespace

In article <3D12C288.20701_at_europe.com>, sg says...
>
>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

IIRC, this is the default. Also, it sounds like you're just accepting predefined database file sizes from the DBCA. If you don't need these sizes (which you probably don't for a small development database) then change them as you build the database. It will be slower to create the database in the first place, though, so you could also just accept the defaults and then use the ALTER DATABASE command as I showed you in my previous posting to shrink it. Then again, if this is a small development instance you shouldn't be running jobs that take that much undo! :)

>
>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.

Howard has answered this separately and as usual is spot on the money so no need to comment here other than to say you can only have one tablespace in this parameter.

>
>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?

It will shrink the undo segments if (when it wakes up) it finds unused extents without active transactions running against that undo segments. As for the 12 hours, that may be release specific (not sure what its setting is for 9i).

>
>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?

If it eats up the entire filesystem, you either have an update running that should be committing more frequently or (as can sometimes be the case for application specific reasons) it doesn't commit at all until the end of the job. Nothing Oracle can do about that, it's the way your application code works that may be "at fault" here (the quotes are because you can't get away from this sometimes). If the wakeup for SMON is still 12 hours, then that's the worst case scenario for how long it will take, but remember if there's still something active you're screwed anyway because SMON can't shrink an active transaction.

As for waking it up manually, again you're looking at making more work for yourself. You're going to have to check every x minutes to see if the shrink has taken place. I'm sure not keen on staying on for 12 hours to do that, so you have to script it. I'd leave it to be done automatically as it should be.

>
>
>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;
>>
>>
>

HTH. Additions and corrections welcome.

Pete

SELECT standard_disclaimer, witty_remark FROM company_requirements; Received on Fri Jun 21 2002 - 10:13:55 CDT