Set Transaction Use Rollback Segment

[kgb]

Hi all
Could you pleas have a look the code bellow and tell me please what I am doing wrong? Looks like transaction goes to wrong rollback segment.
Thanks

SQL> SET TRANSACTION USE ROLLBACK SEGMENT Rollbig;

Transaction set.

SQL> alter table calls drop unused columns;
alter table calls drop unused columns
*
ERROR at line 1:
ORA-01562: failed to extend rollback segment number 1
ORA-01650: unable to extend rollback segment R01 by 4 in tablespace RBS

Oracle 8.1.7.4, Linux RedHat 6.2

[apunhiran]

Hi,
I suppose set traction rollback segment works only with DML transactions and not DDL transactions.
As you are trying to modify the table structure the statement comes under DDL type thats why you are facing this problem.
Thanks

[hrishy]

Hi

That will not work beacuse in Oracle a DDL is processed someyhin like this

commit;
Your DDL
commit;

because oracle silently adds a commit before your sql statememt the set transaction use rollback segment is lost.

one way to do this is offfline all your other rollback segments and make the rollbig as the only rollback segment online and try.

regards
Hrishy

[rajabalachandra]

hi,

is there a way to find
which rollback segment will be used for the next transaction?

-Raja

[pando]

you can use

ALTER TABLE DROP COLUMNS CHECKPOINT XXX

where XXX is number of rows, this issue a commit every XXX rows