confused about rollback segments

[mkey]

Hi all,
I'm getting these following error when I tryed to delete a table which has about 70000 records. Now I do understand that the space available for the rollback segment RB4 is not enough. My question is how can I figured out the size of the rollback segment should be? Secondly is it a better practise to have separate rollback segment for each tablespace in the database? Right now all the rollback segments are pointing to the system tablespace.

ORA-01562 failed to extend rollback segment number string
ORA-01628 max # RB4 extents (500) reached for rollback segment string


Thank you!

 

[rcurva]

Quick way, I assign large Rollback segments in the beginning without setting the Optimal clause. After a few months or week, I check the size and the High-water Mark of the rollback segments (V$ROLLSTAT), from there, you will see how much rollback you might need.

After knowing these info, adjust the size of the rollback segments accordingly, by recreating them.

Robbie

"The rule is, not to besiege walled cities if it can possibly be avoided" -- Art of War

 

[karluk]

Putting nonsystem rollback segments in the system tablespace is a big no-no. You should create a separate rollback segment tablespace, typically called RBS, and put all your rollback segments in it.

 

[rcurva]

I couldn't agree more on Karluk. Putting your segments under System tablespace will definitely fragment your tablespace.

Robbie

"The rule is, not to besiege walled cities if it can possibly be avoided" -- Art of War

 

[mkey]

Thank you for all your responses. Quick question.
I checked the awailable rollback segments on our DBA studio. I see a tablespace
called RBS with rollback segments RBS0 to RBS6. My question is
how can I force the use of these rollback segments to a user?

thanks

 

[Turkbear]

It depends..If the user is doing an ad-hoc transaction or there is a script doing 'stuff' then the FIRST action can( and must) be

SET TRANSACTION USE ROLLBACK SEGMENT RBS06

or whatever...
You cannot, on a user level, assign a 'default' rollback segment that that user will always use..

 

[mkey]

thanks turkbear!