Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: Adding rollback

Re: Adding rollback

From: Howard J. Rogers <>
Date: Wed, 18 Aug 2004 09:46:57 +1000
Message-ID: <4122990e$0$11963$>

bw wrote:

> "Howard J. Rogers" <> wrote in message
> news:<41221dde$0$3928$>...

>> Daniel Morgan wrote:
>> > Howard J. Rogers wrote:
>> >
>> >> Daniel Morgan wrote:
>> >>
>> >>
>> >>>bw wrote:
>> >>>
>> >>>
>> >>>>I have inherited a 9.2.0 database where the only rollback segments
>> >>>>that exist are what was created for SYSTEM. Can I get away with
>> >>>>adding the needed rollback segments (RBS01... etc.) to the init.ora,
>> >>>>creating them in the existing RBS tablespace? Once this has been
>> >>>>completed do I then bounce the instance so they are recognized via
>> >>>>the init.ora?
>> >>>
>> >>>SELECT segment_name
>> >>>FROM dba_undo_extents;
>> >>>
>> >>>Unless this query returns no rows I'd suggest you get yourself
>> >>>current with the 9i and 10g docs.
>> >>>
>> >>
>> >>
>> >>
>> >> Now why would you get him to query that view and in that way?
>> >>
>> >> For meaningful output, you'd surely want him to select DISTINCT
>> >> segment_name from dba_undo_extents.
>> >>
>> >> But then if you were going to do that, you might as well simply select
>> >> segment_name from dba_rollback_segs... a view which is just as
>> >> 'current' in 9i and 10g as any with the buzz-word 'undo' in it.
>> >>
>> >> Regards
>> >> HJR
>> >
>> > Because it was the first query that came to mind that would quickly
>> > indicate that there were undo segments.
>> It was a rhetorical question. The point actually was: dba_rollback_segs
>> tells you all you need to know about UNDO segments, too.
>> In the rush to modernity that is 8i and 9i, let's not throw the 7 and 8.0
>> babies out with the bathwater.
>> > No other reason. And it pointed
>> > the OP to the appropriate data dictionary view.
>> My point exactly. It's a dictionary view, of course. And it has its uses,
>> obviously. But it's not exactly "appropriate" for finding out which undo
>> segments you have (if any) and what they're called.
>> Would you query dba_extents to find out the names of the tables that
>> exist in the database?
>> That's a rhetorical question, too, btw.
>> Regards
>> HJR
> Thanks to all for your help and education.  as it turns out, the
> UNDO_MANAGEMENT param is set to manual and we are running from the
> spfile.  

Well, that's another reason why I prefer to stick to dba_rollback_segs as a view, since it works in both manual and auto environments.

> Im still shaky on ROLLBACK_SEGMENTS as it relates to this 
> issue.  UNDO_MANAGEMENT set to manual and ROLLBACK_SEGMENTS set to
> null in the parameter file are confusing me.  

It shouldn't do. Undo_management=MANUAL means "I will create all my own rollback segments, and do all subsequent management of them". A null value for ROLLBACK_SEGMENTS means "I have not created any of my own rollback segments and I have nothing to subsequently manage".

If you want a read-only database, the apparent contradiction between the two statements is perfectly OK (try some ordinary DML on an ordinary table in your database right now and you should get an error message along the lines of 'can't use system rollback segment for updates in non-system tablespace').

> does oracle still 
> generate the needed rollback based on the calculation of the

Oracle generating anything unspecified rather suggests an 'automatic' setting for something, no? Your undo setting is 'manual'. So no, it won't do anything automatically for you.

What those two parameters you mention do is this: if, at startup, Oracle finds some public rollback segments already created, it will bring as many of them online automatically as it computes are required for normal database operations. And the computation it performs is to take the transactions parameter and divide it by the transactions_per_rollback_segment one. That saves you having to create a bunch of private rollback segments yourself and then laboriously list them in the ROLLBACK_SEGMENTS parameter -because that's the only way a private rollback segment can be brought online when the database is first opened.

So: if you want to list lots of stuff in ROLLBACK_SEGMENTS, you need to do this:

create tablespace RBS datafile 'etc/etc/etc' size whatever EXTENT MANAGEMENT DICTIONARY default storage (initial yM next yM minextents 20);

create rollback segment RB1 tablespace rbs;
create rollback segment RB2 tablespace rbs;
create rollback segment RB3 tablespace rbs;    AND SO ON

And then: ROLLBACK_SEGMENTS=RB1,RB2,RB3 etc etc etc

Or, you can do this:

create tablespace RBS datafile '/etc/etc/etc' size whatever extent management dictionary default storage (initial yM next yM minextents 20);

create public rollback segment RB1 tablespace rbs;
create public rollback segment RB2 tablespace rbs;
create public rollback segment RB3 tablespace rbs;

And then: TRANSACTIONS=10000 (or whatever) TRANSACTIONS_PER_ROLLBACK_SEGMENT=4 (or whatever)

In either case, I've suggested you use dictionary managed tablespace for the rollback segment tablespace. It's a minor thing, but you can't create your first non-system rollback segment in locally-managed tablespace. You'll get the 'can't use the system rollback segment for non-system DML' error I mentioned before if you do. Therefore, using dictionary-managed tablespace is easier. (There is a workaround, which if you decide to go this route, but have to use locally-managed tablespaces, I'll let you know if you ask).

Far, far easier than any of that old nonsense, however, is:

create undo tablespace RBS datafile '/etc/etc/etc' size whatever;

UNDO_TABLESPACE=RBS There's no reason NOT to use automatic undo. There's every reason to do so.

HJR Received on Tue Aug 17 2004 - 18:46:57 CDT

Original text of this message