Re: Adding rollback

bunjibry_at_gmail.com (bw) wrote in message news:<b78d5a20.0408171008.43c7f003_at_posting.google.com>...
> "Howard J. Rogers" <hjr_at_dizwell.com> wrote in message news:<41221dde$0$3928$afc38c87_at_news.optusnet.com.au>...
> > 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. 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. does oracle still
> generate the needed rollback based on the calculation of the
> TRANSACTIONS and TRANSACTIONS_PER_ROLLBACK_SEGMENT parameters?

It will IF you have non-SYSTEM rollback segments available. You do not, as a prior post of yours indicates, therefore you will run into rollback problems.

You can add rollback segments to your database, but you will need a rollback segment tablespace to contain them. Do not use the undo tablespace for this, for if you ever decide to change your method of undo management you will want this UNDO tablespace free of rollback segments. The following example should set you on the proper course:

SQL> create tablespace RBS datafile '<your filespecs here>' size <your size spec here>M;

SQL> create public rollback segment rbs1 tablespace rbs storage (initial 10M next 10M maxextents unlimited);

(repeat above text, substituting a new segment name, for each additional segment you wish to create)

Creating public rollback segments removes the usual necessity of listing them in the init.ora or in the spfile; Oracle will find them quite handily at startup and bring them all online. Non-public rollback segments WILL need to be listed in the ROLLBACK_SEGMENTS parameter if you want them brought online automatically at startup.

Seriously, I would consider changing your method of undo management and utilise the UNDO tablespace. Rollback segments are more trouble than they are worth with the advent of the UNDO tablespace.

David Fitzjarell Received on Tue Aug 17 2004 - 17:04:12 CDT