Re: shrinking undo tablespace

Howard J. Rogers wrote:
>
> "sg" <s4v4g3_at_europe.com> wrote in message news:3D12C288.20701_at_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.
>
> Now, now! I said that a *database* could have more than one undo_tablespace.
> I didn't say that more than one could be active at a time! Although, in
> fact, even the statement that a database can only have one active undo
> tablespace at a time is not quite right: when you switch from tablespace A
> to tablespace B, new transactions use B, but anything already running using
> A is allowed to finish in A.
>
> Until it all goes quiet on A, there *are* two active undo tablespaces.
>
> But that's a special case. In general, it's one instance, one
> undo_tablespace. But there can be any number of undo_tablespaces in the
> database. And you can then switch from one to another at will.
>
> Regards
> HJR
>
> >
> > 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;
> > >
> > >
> >

One (awful) option could be to set undo_retention to a very low value, wait for the db to agressively chuck out all the undo and just hope that the undo segments are toward the front of the file...

...Ugh!

-- 
==============================
Connor McDonald

http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue..."