Rollback segment problem
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 5 123 ... LastLast
Results 1 to 10 of 44

Thread: Rollback segment problem

  1. #1
    Join Date
    Nov 2004
    Location
    Mumbai, India
    Posts
    452

    Rollback segment problem

    Hi everybody,

    Facing an age old problem
    I am running a delete statement in a cursor (to commit in between)
    I am commiting every 1000 records. The pl/sql block would delete about 8 million records.My undo management is automatic
    Some stats:
    table size : 1.2 GB
    undo tablespace size 1 GB
    Records to be deleted : about 8 million
    commit : every 1000 records
    undo retention: 900

    Still it gives the error
    ORA-01555: snapshot too old: rollback segment number 11 with name "_SYSSMU11$"
    I have a hunch that my undo retention has to be increased.
    If i am correct then about how much should i increase
    Any help will be appreciated
    There are three kinds of lies: Lies, damned lies, and benchmarks...

    Unix is user friendly. It's just very particular about who it's friends are.

    Oracle DBA

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    are you fetching across a commit - cos that would be naughty

    post the code

  3. #3
    Join Date
    Nov 2004
    Location
    Mumbai, India
    Posts
    452
    Declare
    Cursor del_row is select rowid from temp_tab a where exists
    (select 1 from temp_tab b where ((a.amount>0
    and b.AMOUNT<0) or(a.amount<0 and b.AMOUNT>0))
    and abs(a.amount)=abs(b.AMOUNT)
    and a.md=b.MD
    and a.comment=b.COMMENT
    and a.type=b.TYPE);
    ctr number:=0;
    Begin
    for one_row in del_row loop
    delete temp_tab where rowid=one_row.rowid;
    ctr:=ctr+1;
    if ctr=1000 then
    commit;
    dbms_output.put_line('1000 rows commited');
    ctr:=0;
    end if;
    end loop;
    exception
    when others then
    dbms_output.put_line(sqlerrm);
    end;
    There are three kinds of lies: Lies, damned lies, and benchmarks...

    Unix is user friendly. It's just very particular about who it's friends are.

    Oracle DBA

  4. #4
    Join Date
    Nov 2004
    Location
    Mumbai, India
    Posts
    452
    PS: the query is doing an index range scan for the sub query and FFS for the parent.
    I am forcing parallel DML also,i.e.,alter session force parallel DML.
    There are three kinds of lies: Lies, damned lies, and benchmarks...

    Unix is user friendly. It's just very particular about who it's friends are.

    Oracle DBA

  5. #5
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    3,997
    So you really want to use a collection to store the primary key of all the records you want to delete.
    this space intentionally left blank

  6. #6
    Join Date
    Jan 2002
    Location
    Netherlands
    Posts
    1,587
    Yuo don't have AUM on?
    Tarry Singh
    I'm a JOLE(JavaOracleLinuxEnthusiast)
    TarryBlogging
    --- Everything was meant to be---

  7. #7
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    well you are fetching across a commit - that is a such a bad thing to do and can guarantee yourself a ORA-1555 which you have got

  8. #8
    Join Date
    Jan 2002
    Location
    Netherlands
    Posts
    1,587
    http://asktom.oracle.com/pls/ask/f?p...D:546822742166
    so had you set your undo to 10G maybe you'd have escaped the error.
    Tarry Singh
    I'm a JOLE(JavaOracleLinuxEnthusiast)
    TarryBlogging
    --- Everything was meant to be---

  9. #9
    Join Date
    Nov 2004
    Location
    Mumbai, India
    Posts
    452
    Originally posted by davey23uk
    well you are fetching across a commit - that is a such a bad thing to do and can guarantee yourself a ORA-1555 which you have got
    I ran the delete without the commit also :
    Delete from temp_tab a where exists
    (select 1 from temp_tab b where ((a.amount>0
    and b.AMOUNT<0) or(a.amount<0 and b.AMOUNT>0))
    and abs(a.amount)=abs(b.AMOUNT)
    and a.md=b.MD
    and a.comment=b.COMMENT
    and a.type=b.TYPE);
    Got the same error
    Latest update is :
    Set undo retention to 5000
    commit every 5000 rows instead of 1000
    Any solutions??
    Am really in a fix with this
    Last edited by simply_dba; 04-28-2005 at 12:30 PM.
    There are three kinds of lies: Lies, damned lies, and benchmarks...

    Unix is user friendly. It's just very particular about who it's friends are.

    Oracle DBA

  10. #10
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    3,997
    Originally posted by simply_dba
    I ran the delete without the commit also :

    Got the same error
    Latest update is :
    Set undo retention to 5000
    commit every 5000 rows instead of 1000
    Any solutions??
    Am really in a fix with this
    Load the data into a collection, then you can either do a bulk bind delete with only one commit or you can iterate through the collection and do as many commits as you like. Once you load the data into a collection there is no problem maintaining a read consistant view of the table which is what usually causes the ORA-1555 errors.
    this space intentionally left blank

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