Rollback segment error
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Rollback segment error

  1. #1
    Join Date
    May 2001
    Location
    London
    Posts
    149

    Rollback segment error

    Hello,

    I am executing one sql file which populates our tables in database from other schema. My sql file contents are as follows.
    Each procedure populates one table.

    begin
    set transaction use rollback segment BIG_R01;
    -- EOD tables will be populated here
    PKG_REP_EOD.prc_princ_co_share_hist;
    PKG_REP_EOD.prc_unalloc_share_balances;
    PKG_REP_EOD.prc_distribution_percentages;
    PKG_REP_EOD.prc_company_trusts;
    pkg_rep_eod.prc_historic_share_price;
    end;
    /

    Evenif I am assigning big rollback segment why its giving following error.

    ORA-01562: failed to extend rollback segment number 5
    ORA-01628: max # extents (500) reached for rollback segment R04

    Thanks

    Paresh

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,331
    isnt the error obvious, the your rollback segment has exceeded its maximum number of extents

  3. #3
    Join Date
    May 2001
    Location
    London
    Posts
    149
    But I am assigning Rollback Segment BIG_R01, then why it is using Rollback segment R04...Or do I have to specify Big_R01 for all Procedures. Because few procedures are getting executed properly...
    I am getting error for last few procedures....

    begin
    set transaction use rollback segment BIG_R01;
    PKG_REP_EOD.prc_princ_co_share_hist;

    set transaction use rollback segment BIG_R01;
    PKG_REP_EOD.prc_unalloc_share_balances;

    set transaction use rollback segment BIG_R01;
    PKG_REP_EOD.prc_distribution_percentages;
    ..............
    end;
    /

    In every procedure I am deleting data in table and inserting data into table and then commiting it. So after commit, I think Oracle doesnt use BIG_R01 and its using any randowm Rollback segment and thats why i m getting error. Is my understanding is right???

    Thanks

    Paresh

  4. #4
    Join Date
    Dec 2002
    Location
    chennai
    Posts
    12
    why dont u take the other rollback segs offline and check out.

  5. #5
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by pareshg
    So after commit, I think Oracle doesnt use BIG_R01 and its using any randowm Rollback segment and thats why i m getting error. Is my understanding is right???
    Yes, that's exactly how it happens. SET TRANSACTION USE ROLLBACK SEGMENT is in effect only untill the current transaction finishes. So after every COMMIT or ROLLBACK you have to set transaction to a specific RB segment again if you wan't that new transaction to be tied with the specific RB.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  6. #6
    Join Date
    May 2001
    Location
    London
    Posts
    149
    Thanks guys...

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