I am trying to delete some records from a table and it takes a few hours to finish the process. However, I always ended with the following error:
ORA-01562: failed to extend rollback segment number 12
ORA-01628: max # extents (121) reached for rollback segment R012
The rollback segment is always different when I execute the sql. I have tried to extend the rollback segment already. May I know if I can create a rollback segment and delete the records using that rollback segment only? After finishing the deletion, I will remove the rollback segment. Is this possible? If not which rollback segment maxextents should I increase? Thanks!
set transaction use rollback segment xxx;
this must be the first statement o f sql.
So create e big rbs ...and when fish drop it.
are you shore that the rollback sgmnt is connected, as far as i know you can run into rollback problems only in update. in insert and delete, you do not write to rollback
Shawish, you are wrong. Rollabck segment will be used fot all DML activity not just for updates.
CREATE ROLLBACK SEGMENT BIG_RBS STORAGE(INITIAL 1M NEXT 1M OPTIMAL 1M MINEXTENTS 2);
ALTER ROLLBACK SEGMENT BIG_RBS ONLINE;
**drop indexes on the delete table if appropriate**
SET TRANSACTION USE ROLLBACK SEGMENT BIG_RBS;
DELETE FROM ******** WHERE **********;
ALTER ROLLBACK SEGMENT BIG_RBS SHRINK;
ALTER ROLLBACK SEGMENT BIG_RBS OFFLINE;