| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: shrinking undo tablespace
then basically with undo we are totally out of control...... probably
time will tell if this advantageous or the invert :-)
Pete Sharman wrote:
> 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 >>
>>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; >>> >>> >>>
 |
 |