RE: Rollback Segments

From: A. Bardeen <abardeen1_at_yahoo.com>
Date: Wed, 09 May 2001 10:23:24 -0700
Message-ID: <F001.002FE110.20010509094207@fatcity.com>

Kevin,

This is a common misconception.

On startup Oracle determines the number of rollback segments to bring on line based on the init.ora parameters:
  TRANSACTIONS / TRANSACTIONS_PER_ROLLBACK_SEGMENT If the number of rollback segments specified by ROLLBACK_SEGMENTS is less than that value, then Oracle automatically will bring online any other PUBLIC rollback segments to bring the total of online rollback segments up to that value. Any PUBLIC rollback segments remaining will be left offline. PRIVATE rollback segments will only be brought online automatically if specified in the ROLLBACK_SEGMENTS parameter.

Misconception #2: TRANSACTIONS_PER_ROLLBACK_SEGMENT determines the number of concurrent transactions that can use a rollback segment. Granted that's what the name implies, but this isn't the case. This parameter is used only to determine the number of PUBLIC rollback segments to bring on line automatically at startup. The number of concurrent transactions that can use a rollback segment is based on many factors, the most important one being DB_BLOCK_SIZE.

I recommend using private rollback segments and specifying them in ROLLBACK_SEGMENTS because it makes it much easier to handle rollback segment problems. If there's a problem with a rollback segment on startup, for example, simply removing the segment from the init.ora will usually allow you to at least get the db open.

HTH,

• Anita
  • Kevin Kostyszyn <kevin_at_dulcian.com> wrote:
    > Hi all,
    > This brings me to a stupid question then. What is
    > the true difference
    > between a public and a private rollback segment?
    > Because if I just think
    > about it like a normal human being I would think
    > that if a user made a
    > Private rollback segment then only that user could
    > use it, but of course I
    > know that can't be the case.
    > Kev
    >
    > -----Original Message-----
    > Sent: Wednesday, May 09, 2001 10:26 AM
    > To: Multiple recipients of list ORACLE-L
    >
    >
    >
    > Nixon,
    >
    > While SET TRANSACTION can be used to assign a
    > rollback
    > segment to a transaction, it cannot be assigned
    > exclusively to
    > that transaction as you have found.
    >
    > The ability to assign a rollback segment to a
    > transaction and
    > make it read-only for all others is something I've
    > wanted myself
    > for many years, and I know it's been on the official
    > 'wish list' of
    > requested changes by IOUG for some time.
    >
    > Maybe 9i? Anyone know?
    >
    > Jared
    >
    >
    > On Wednesday 09 May 2001 01:40,
    > Nixon_Villanueva_at_manulife.com wrote:
    > > Hi All,
    > >
    > > Here is my environment;
    > >
    > > NT v4
    > > Db Oracle Workgroup v8.1.6
    > >
    > > Rollback Segments
    > > SEGMENT_NAME OWNER
    > TABLESPACE_NAME
    > > ------------------------------ ------
    > ------------------------------
    > > SYSTEM SYS SYSTEM
    > > RBS0 PUBLIC RBS
    > > RBS_P1 SYS RBS
    > > RBS_P2 SYS RBS
    > > RBS_P3 SYS RBS
    > > RBS_P4 SYS RBS
    > > RBS_P5 SYS RBS
    > >
    > >
    > > Is it possible to assign one public rollback
    > segment explicitly to one
    > > particular transaction? I tried using SET
    > TRANSACTION USE ROLLBACK SEGMENT
    > > but other users I found out can still use it. So,
    > I guess the best
    > > alternative is to set that RB segment to OFFLINE
    > after using and set
    > ONLINE
    > > before executing the SET TRANSACTION ... Do you
    > know how it can be done
    > > inside Forms?
    > >
    > > Thanks in advance!
    

    ---

Do You Yahoo!?
Yahoo! Auctions - buy the things you want at great prices http://auctions.yahoo.com/

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: A. Bardeen
  INET: abardeen1_at_yahoo.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Wed May 09 2001 - 12:23:24 CDT