DBAsupport.com Forums - Powered by vBulletin
Page 3 of 5 FirstFirst 12345 LastLast
Results 21 to 30 of 44

Thread: Rollback segment problem

  1. #21
    Join Date
    Nov 2004
    Location
    Mumbai, India
    Posts
    452
    Originally posted by DaPi
    If I'm right about your business needs, you're deleting pairs of matching entries (transactions?):

    1) The cursor "selects" 8mio rows including a pair +123 and -123

    2) You loop & commmit several times, and by chance this deletes the row +123 & commits, by chance -123 hasn't be processed yet.

    3) It crashes with a 1555

    4) You restart. The cursor is re-evaluated and "selects" 7mio rows BUT DOES NOT INCLUDE -123 because this is not a paired entry any more; its matching entry no longer exists.

    BINGO - your books don't balance
    Yeah you are right.That's why I am doing it on a cloned table instead of the original one.After the procedure is succesful, I will run it on the original table.
    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. #22
    Join Date
    Nov 2004
    Location
    Mumbai, India
    Posts
    452
    Originally posted by gandolf989
    Let's just be friends!
    You don't want to make Mr Hanky angry do you??
    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

  3. #23
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Originally posted by simply_dba
    After the procedure is succesful, I will run it on the original table.
    The problem is that a 1555 will depend on the work-load, db configuration etc. So a successful test-run does NOT guarantee success in production.
    "The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman

  4. #24
    Join Date
    Nov 2004
    Location
    Mumbai, India
    Posts
    452
    Originally posted by DaPi
    The problem is that a 1555 will depend on the work-load, db configuration etc. So a successful test-run does NOT guarantee success in production.
    That's true. but now I have developed a procedure to delete using collection ( A DBA doing a Developer's work. I wonder what Mr Hanky will say) storing all the rowids in another table.I think this ay I can ensure data consistency. Even if I ran into 1555.
    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. #25
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Originally posted by simply_dba
    . . .but now I have developed a procedure to delete using collection . . . . storing all the rowids in another table.
    Sounds good to me.
    "The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman

  6. #26
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    cant you do smth like this?

    Code:
    loop
    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)
    and rownum <= 100000;
    commit;
    exit when sql%rowcount < 100000;
    end loop;

  7. #27
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Originally posted by pando
    cant you do smth like this?
    NO ! ! ! !

    If my reading is right (simply_dba hasn't said otherwise) the requirement is to delete pairs of matching rows. This technique could delete one of a pair in the first loop and would NOT select the second in subsequent loops beacuse it no longer matches.
    "The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman

  8. #28
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    yes

    it might work if the number of matching rows is not bigger than 100000 rows correct?

  9. #29
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Originally posted by pando
    yes

    it might work if the number of matching rows is not bigger than 100000 rows correct?
    If it's less than 100000 you don't need a loop.
    If it's more than 100000 you risk your data integrity.
    "The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman

  10. #30
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    I wonder how no one asked.... wat is the number of records you want to retain?

    If you are trying to delete more than 25% of records from a table.. go for parallel insert into temp table the records you want to retain.. drop main table.. rename temp table to main table..

    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

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