Rollback Segments / Extents

[benjamenus]

My database uses several rollback segments, this is their normal state:

RB1 - Online - Small in size
RB2 - Online - Small
RB3 - Offline - Large

I have a large update to perform at night. I schedule a batch file which opens SQLPlus and logs in to the database. It then runs an SQL script which begins like this:

spool C:\test.log

connect x/y@z

update sys.undo$
set sys.undo$.status$=2
where sys.undo$.name='RB1';

update sys.undo$
set sys.undo$.status$=2
where sys.undo$.name='RB2';

update sys.undo$
set sys.undo$.status$=3
where sys.undo$.name='RB3';

It then follows with the update SQL.

The idea is that RB1 and RB2 are taken offline and RB3 is put online. However, when I checked the log this morning I had the following error:

update TABLE
*
ERROR at line 1:
ORA-01562: failed to extend rollback segment number 1
ORA-01628: max # extents (505) reached for rollback segment RB1

I was a little surprised as I though RB1 should've been offline. Does anyone know why this happened and how to avoid it? Would using a separate script to change the status of rollback segments prior to running the update work?

Cheers

 

[benjamenus]

Sorry - there is also a SYSTEM rollback segment which is always online ...

 

[sem]

Use

set transaction use rollback segment RB3

In this case you do not need to turn other segments offline.

It's also generally not a good idea to disable rollback segments by updating sys.undo$

 

[benjamenus]

Cheers sem

Just wondering - I need to put RB3 online first. If I update sys.undo$ to put RB3 online and in the same script then use set transaction, will this work?

The reason I ask is because it appears that the update sys.undo$ did not work!

Thanks ....

 

[sem]

Unfortunately I have about no experience in making things better by updating SYS tables
I'm not sure at all that this leads to the same results as using an appropriate ALTER command. I can also assume that changing of STATUS$ field is the one but not the only consequence of ALTER ROLLBACK SEGMENT command. As you find it to be suitable to make changes under SYS account you probably more experienced then me. I have no database for such tests for the probability to loose it in such experiments is very high.

In any case you SET TRANSACTION statement must be the first in transaction, I mean you should commit or rollback before issuing it.

 

[benjamenus]

OK, I'll giv it a go :-0

 

[benjamenus]

sem

thanks for your help - turned out to be wise words indeed. Normally I change the status of RB segments through an Oracle8 GUI. When I updated sys.undo$ the icons showed the RB segments as having changed. However, when I used the set transaction command, it would still report the segment as being offline! However - everything works fine using the alter command.

I'd be interested to know if anyone has further info on this.

Thanks again

Mark

 

[sem]

In fact set transaction can not change segment status, it just produces an error if the required segment is offline. To dedicate a segment to your transaction you need not turn segments offline.

The icon is changed because your tool obviously queries the same table. But as I've already mentioned it may be only visible part of an iceberg.