Re: Rollback Segments

"Will C." <whc2u_at_hotmail.com> wrote in message news:84c3d20c.0106101213.2b7f2c45_at_posting.google.com...
> Hi,
>
> I have a couple of questions about rollback segments. Any help is
> appricated. So, my System: Oracle 8.1.6 on Linux
>
> Question 1:
> According to Technet, if I don't specify rollback segments
> in my init.ora file, the Oracle instance will automatically aquire the
> rollback segments it needs (from those that are avaiable). At the
> moment, I'm refering to PUBLIC rollback segments. Technet also
> states that the SYSTEM rollback segment is aquired automaticaly
> as well and that if there are too many transactions for the
> non-SYSTEM rollback segments, Oracle will use the SYSTEM
> segment. So, if this occurs, and I create and bring online additional
> rollback segments, will they automatically be utilized by the
> instance, or will I have to shutdown and restart the instance?
>

First, the SYSTEM rollback segment can not be used for transactions in non-SYSTEM tablespace, so I have no idea what that technet reference is trying to say. If it says that the SYSTEM rollback segment can ever hold rollback for DML in a USERS or DATA tablespace, it's just wrong.

Otherwise, if you create a new rollback segment, then you must 'alter rollback segment newone online'. At that point, it becomes available for use by any NEW transactions. Since a transaction must finish in the same segment as it starts in, no existing segments can suddenly start using blocks from any new ones you create.

> Question 2:
> If I specify the rollback segments I want the instance
> to use in init.ora, do I need to specify system segments as well,
> or only just the public/private rollback segments? (ie, are
> SYSTEM rollback segments always automatically aquired?)

There is one and only one SYSTEM rollback segment, and yes, it is always acquired by the Instance at startup. Be clear what it is for, however. It is to hold the rollback of any DML performed on the data dictionary tables. Hence, if you create a new table, create an index, or do an insert that requires a segment to acquire a new extent -all those things require inserts and deletes on the data dictionary, and *that's* what the SYSTEM rollback segment is for. Hence, were an Instance *ever* to fail to acquire such a segment automatically, you would be in deep, deep trouble.

>
> Question 3:
> If I added an additional SYSTEM rollback segment and bring it
> online, its it utilized automatically?
>

You cannot, ever, create another SYSTEM rollback segment. The fact that a rollback segment happens to be housed in the SYSTEM tablespace does NOT make it the SYSTEM rollback segment. Hence, ALL other rollback segments, whatever tablespace they are created in, are non-SYSTEM rollback segments, and as such must be brought online either manually with the 'alter rollback segment ... online' command, or by editing the init.ora file.

There is one and only one SYSTEM rollback segment, and it is the one created for you automatically as part of the 'CREATE DATABASE' command. It has the name 'SYSTEM', and it's housed in the 'SYSTEM' tablespace. No other rollback segment can ever satisfy those three requirements, and hence no other segment can ever be the SYSTEM rollback segment.

And despite some comments posted by [an]other poster(s) here, it is point-blank utterly impossible ever to create a database without that SYSTEM rollback segment.

Regards
HJR
> Much Thanks,
>
> Will C.
Received on Mon Jun 11 2001 - 00:37:37 CDT