Rollback Segment too small
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Rollback Segment too small

  1. #1
    Join Date
    Jan 2000
    Posts
    387

    Lightbulb

    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!

  2. #2
    Join Date
    Apr 2000
    Location
    roma
    Posts
    131
    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.

  3. #3
    Join Date
    Jul 2000
    Posts
    243
    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
    shawish_sababa

    shawish_sababa@hotmail.com

  4. #4
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    Hello!

    Shawish, you are wrong. Rollabck segment will be used fot all DML activity not just for updates.

  5. #5
    Join Date
    Oct 2000
    Posts
    80
    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;
    John Doyle

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width