Re: shrinking undo tablespace

"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;
> >
> >
>
Received on Fri Jun 21 2002 - 01:56:24 CDT