Set Transaction Use Rollback Segment

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

Code:

---

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

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

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

hi,

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

-Raja

you can use

ALTER TABLE DROP COLUMNS CHECKPOINT XXX

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