Re: shrinking undo tablespace

The real point is that you never were really very well in control of rollback. There was just a lot to fuss about, and you had to resort to ways around things. Now Oracle looks after the lot for you, and the only price you pay is disk space.

Sounds like a fair deal to me.

Regards
HJR "sg" <s4v4g3_at_europe.com> wrote in message news:3D13B48F.90001_at_europe.com...
> 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
> >>
> >
> > 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 Sat Jun 22 2002 - 01:22:55 CDT