-
Hi,
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.
-
Hi mooks
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
-
Hello!
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**
COMMIT;
SET TRANSACTION USE ROLLBACK SEGMENT BIG_RBS;
DELETE FROM ******** WHERE **********;
ALTER ROLLBACK SEGMENT BIG_RBS SHRINK;
ALTER ROLLBACK SEGMENT BIG_RBS OFFLINE;
