Rollback segment..

[dguywill]

Hi All,
I am running into some problems running a script that has a save point. In the script I am deleting from tables with the use of a cursor. The funny thing is that it works fine when I delete like about 100 rows, but then it fails when I delete like thousands of rows. The error it gives is this:

ora-01555 snapshot too old rollback segment with name RB4 too small..
ora- savepoint never established..

I am wondering what course of action I should take. the options I know of is to defracgment the databse or to increase the size of the rollback segment.

Also, if the deleting the rows is my only transaction in the script, should I take out the savepoint? WIthout the savepoint, if it fails to delete, will it rollback? Is it the same rollback segment that will be used without a savepoint? I have only one Rollback tablespace..

Thanks in advance,
Dee.

[Halo]

Increase the size of the rbs. Yes, remove the savepoint (it's only a reference point in a transaction). Yes, if it fails to delete, the transaction will rollback. Oracle randomly selects a rbs when a transaction begins. If you want it to use a specific rbs, then issue: set transaction use rollback segment (name); before beginning the transaction.