rollback segment question

[puneet]

Hi

Should SYSTEM RB have an optimal value set like all other Rollback segments . Please let me know

Thanks

[stecal]

Oracle automatically creates the SYSTEM rollback segment and it can never be dropped. It is never listed in the init.ora file. So, no, there is no "optimal" size for it - it is managed by Oracle.

[puneet]

Should I put an Optimal size for it , If I do what harm can I cause to the Database performace

regards

[pando]

it will be never used after your database creation, why you wanna do that???

[stecal]

Again, there is no optimal size for it - it is managed by Oracle. All you can do is sit back and watch.

[puneet]

Some of my users had their default Tablespaces to system ,
I was under the impression that if the default tablespace is system , it might use the "SYSTEM RB" for work

I think I am wrong here .

I get the point that the SYSTEM RB will never be used after DB creation .

Please confirm my misunderstanding here

Thanks a lot guys !!

[thanigai]

Yes You are mistaken........
What is the error you are getting???.Meaning what kind of error made you to come to this conclusion....
System rb is used only once at the time of db creation.Thats it.After that temp or what ever you name....rbs1,2,3...

Your Users created without specifying default tablespace.
Thats why default tablespace is SYSTEM.Better change their default tablespace from system.It is not a good practice.
Thanks.
Thanigaivasan.

[kerlion]

keep in mind that
never let any application or users to user system tablespace,
if you had, alter user default tablespace and quota immediately, and move all tables and indexes out of system tablespace, action now!

[clio_usa]

Originally posted by thanigai
Yes You are mistaken........
System rb is used only once at the time of db creation.Thats it.After that temp or what ever you name....rbs1,2,3...

No quite right, Thanigai

Here is how the rollback segment in the SYSTEM tablespace is used:

When a database is first created using the CREATE DATABASE command, only a single rollback segment is created. This is the system rollback segment and it is created in the system tablespace. The system rollback segment has one basic difference from any other rollback segment, including any other rollback segments that are created in the system tablespace. This difference is that the system rollback segment can only be used for transactions that occur on objects inside the system tablespace. This is done because the main purpose of the system rollback segment is to handle rollback for DDL transactions - that is transactions against the data dictionary tables themselves. Making the system rollback usable only for the system tablespace was simply an easy way to enforce that. It is possible for the system rollback segment to be used for non-data dictionary tables, but only if those tables are created inside the
system tablespace (which is very bad development practice).

Rollback segment in the SYSTEM tablespace is not used only once at db creation, but for the life of the database. Actually, every time you do DDL on the data dictionary (running catproc.sql, catalog.sql, etc...).

Check the latest Oracle DBA forums for more details:
http://www.dbaclick.com/cgi-bin/ib3/ikonboard.cgi


Hope that hopes,



--clio_usa
Senior Oracle DBA

[SANJAY_G]

To clio_usa:

Are you sure that DDL statements use rollback segment?

Sanjay