Results 1 to 11 of 11
  1. #1
    Join Date
    Nov 2004
    Posts
    35

    Unanswered: rollback after a commit

    is it possible to rollback after a commit?
    can a savepoint work if i save before the commit.
    or any idea is it possible to commit after 10k of records?
    i am just afraid that the db might hang due to commit of too many records..
    thanks in advance

  2. #2
    Join Date
    Jun 2004
    Posts
    796
    Provided Answers: 1
    This link explains about savepoints, rollback etc:

    http://java.cms.livjm.ac.uk/oracle9i...0/adg08sql.htm


    As for how long before your commit will fail, I'm afraid I don't know the answer to that one but I'm sure someone else does....
    90% of users' problems can be resolved by punching them - the other 10% by switching off their PCs.

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    > is it possible to rollback after a commit?
    No

    > can a savepoint work if i save before the commit.
    It doesn't help you to rollback after a commit, if that is what you mean. A savepoint is just a marker that allows you to perform a partial rollback before a commit.

    > or any idea is it possible to commit after 10k of records?
    Yes, you can count the records and commit periodically, but it isn't a good idea to do so

    > i am just afraid that the db might hang due to commit of too many records.
    Actually, committing is easy - it is rollback that takes time.

    What you do need is enough UNDO space for the size of your transaction.

  4. #4
    Join Date
    Nov 2004
    Posts
    35
    hmm... maybe i shld rephrase my qns
    lets say i have these code.
    if a=true then
    some code
    commit
    end if;
    if b= true then
    some code
    else
    rollback
    end if;
    is it possible to roll back to before the commit?
    i think the ans is no.
    haha.
    can i like update 10k of record at a go? cos i think the max record should be slightly less than 10k. i dun wish to do a counter as it waste time.

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    You are right - COMMIT is final.

    Updating 10K records in a single transaction should be fine.

  6. #6
    Join Date
    Jan 2004
    Posts
    492
    Though for what its worth, implementing a counter, or even using the built in cursor row counts won't take any extra time. Its going to take Oracle the same time, if not longer to check if a= true or b=true than it would to just augment a counter each time through the loop.
    Oracle OCPI (Certified Practicing Idiot)

  7. #7
    Join Date
    Jun 2003
    Location
    Ottawa
    Posts
    105

    a little peeve....

    redundant:

    Code:
    if a = true then
       code;
    end if;
    clean:

    Code:
    if a then
       code;
    end if;
    You're obsessed and distressed 'cuz you can't make any sense
    Of the ludicrous nonsense and incipient senescence
    That will deem your common sense useless

  8. #8
    Join Date
    Jul 2003
    Posts
    2,296
    why commit at all???
    conduct all your transactions and only commit at the closing of the procedure.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  9. #9
    Join Date
    Dec 2003
    Posts
    1,074
    A rule that I subscribe to is:

    If you cannot commit at the end of your transaction because of a lack of UNDO (or rollback segments, depending on your implementation), then don't code around it, but rather talk to the DBA and get more UNDO space.

    -cf

  10. #10
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    if a=true then
    some code
    commit
    end if;
    if b= true then
    some code
    else
    rollback
    end if;
    In the second case (b), there is no commit or rollback. So if I execute a procedure which modifies some values, then the above code, then decide to roll back, what state is my data in?

  11. #11
    Join Date
    Nov 2004
    Posts
    35
    ok..
    thanks all for the information..

Posting Permissions

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