Help! commit not working
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Help! commit not working

  1. #1
    Join Date
    Aug 2000
    Posts
    143
    I have a large table (23 million records) that im trying to update using a plsql block. This commits after updating every record in the block.

    FOR equityprice_rec in equityprice_cur
    LOOP
    newDate := equityprice_rec.tradingdate +
    daysDifference;

    UPDATE equityprice
    SET tradingdate = newDate
    WHERE current of equityprice_cur;
    commit;
    END LOOP;

    But still I keep getting an error message like the following:

    ERROR at line 1:
    ORA-01562: failed to extend rollback segment number 12
    ORA-01628: max # extents (70) reached for rollback segment R11
    ORA-06512: at "GIRS.UPDATE_EQUITY_DATE", line 4
    ORA-06512: at "GIRS.UPDATE_EQUITY_DATE", line 20
    ORA-06512: at line 1

    Does anyone know why the commit isnt working?

  2. #2
    Join Date
    Mar 2001
    Posts
    635
    Hi

    Try to commit after every 1000 records

    Regards
    Santosh

  3. #3
    Join Date
    Jun 2001
    Posts
    76
    Or you can:

    1) Increase disk space
    2) Set your rollback space to expand indefinately

    R
    On the other hand, you have different fingers.

  4. #4
    Join Date
    Sep 2000
    Location
    Calcutta / Ahmedabad, India
    Posts
    137
    Hi !

    it's got nothing to do with your commit statement . Create a large rollback segment eith maxextents set to a very high number or to unlimited. And then set the transaction to that rollback segment.

    The problem is that your rollback segment is simply runnng out of space.

    Suva
    Suvashish

  5. #5
    Join Date
    Aug 2000
    Posts
    143
    I thought that commiting after every record should cause only one transaction to be stored in the rollback segment. AFter commiting I cannot rollback anyway.

  6. #6
    Join Date
    Jul 2000
    Posts
    243
    Hi

    first, how many lines o you need to update? if, for example, we are tolking about 1k lines that every line is 1k in average, then you should created be chngeing the size of your rollback segments. befor you do that. read from this site about the issue.
    if, on the other end, we have 1000k lines then you can: 1) use dsuva proposal. 2) use santoshym proposal. i would personaly use commit every x lines.

  7. #7
    Join Date
    Jun 2001
    Posts
    316
    I had faced with same kinda problem

    What I did was...

    This was the seq of steps i did in panic!!!!

    connect as sys user and kill all on going sessions

    commit;

    alter tablespace RBS default storage (pctincrease 1);
    alter tablespace RBS default storage (pctincrease 0);
    alter tablespace RBS coalesce;

    I dunno if it is rite...
    But It dint screw up my database

  8. #8
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Whatever have been proposed here in this thread does not answer the original question. All proposed solutions missed one cruicial point: transaction is updating a single column (tradingdate) of a *single record* (WHERE CURRENT OF equityprice_cur), after which a commit is isued. So transaction is realy extremely short one and it *should not* cause any ORA-1628!!! Not even in theory! The sizing of rollback segment has nothing to do with what this PL/SQL block *should* be doing.

    But obviously something is hidden here, something must be missing from the whole picture.

    mb, is there any trigger on that table that is cusing mass changes to some table(s) whenever an update is executed on EQUITYPRICE table? That is the only thing that I could think of that might be causing your error.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  9. #9
    Join Date
    Aug 2000
    Posts
    143
    There are no triggers, but the table has 23 million records in it. Someone has suggested the problem may be because I use:

    cursor equityprice_cur is
    SELECT tradingdate
    FROM equityprice
    FOR UPDATE;

    Oracle may be storing all of the entries of trading date in the rollback segments, which may mean that the rollback segments need to HUGE.

    If this is true does anyone know of a work around?

  10. #10
    Join Date
    Jul 2000
    Posts
    243
    how many times this loop reapets itself?

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