DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: Ora 01591

  1. #1
    Join Date
    Aug 2007
    Location
    Cyberjaya,kuala lumpur
    Posts
    340

    Ora 01591

    when i am running the procedure i am getting this error

    ORA 01591 lock held by in-doubt distributed transaction

    And i commited the transaction manually, but again it is comming.
    Is there any problem in the code.

    here i am attaching the procedure
    Attached Files Attached Files

  2. #2
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,492

    Cool Rollback

    Check DBA_2PC_PENDING and dba_2pc_neighbors for distributed transaction id's.

    If you find rows there, do:

    Code:
    ROLLBACK WORK FORCE '{transaction id.}';
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  3. #3
    Join Date
    Nov 2001
    Posts
    335
    Use this if ROLLBACK WORK FORCE '{transaction id.}'; does not work

    SQL> alter system set undo_suppress_errors=true sid='XXXXX';



    SQL> set transaction use rollback segment system;



    Transaction set.



    SQL> exec dbms_transaction.purge_lost_db_entry('74.42.1112345');



    PL/SQL procedure successfully completed.



    SQL> commit;



    Commit complete.
    One, who thinks that the other one who thinks that know and does not know, does not know either!

  4. #4
    Join Date
    Aug 2007
    Location
    Cyberjaya,kuala lumpur
    Posts
    340
    Thanks for Both of them,

    I will try it

  5. #5
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    Quote Originally Posted by BV1963
    Use this if ROLLBACK WORK FORCE '{transaction id.}'; does not work

    SQL> alter system set undo_suppress_errors=true sid='XXXXX';



    SQL> set transaction use rollback segment system;



    Transaction set.



    SQL> exec dbms_transaction.purge_lost_db_entry('74.42.1112345');



    PL/SQL procedure successfully completed.



    SQL> commit;



    Commit complete.

    my god - what a load of rubbish, you cannot set transation like that!! all you are doing is supressing the error it gives

    gee whizz

  6. #6
    Join Date
    Aug 2007
    Location
    Cyberjaya,kuala lumpur
    Posts
    340
    Hi davey,

    when i got the error first

    i followed the steps which i got from the metalink.

    select LOCAL_TRAN_ID, TRAN_COMMENT,STATE,STATUS from pending_trans$;


    SELECT LOCAL_TRAN_ID, GLOBAL_TRAN_ID, STATE, MIXED, HOST, COMMIT#
    FROM DBA_2PC_PENDING
    WHERE LOCAL_TRAN_ID ='10.25.36897';


    COMMIT FORCE '10.25.36897', '10146005246693';

    after commiting the transaction i am able to insert.
    but the same error is comming again...So what steps i can take for stoping it
    I think it is the problem with the aplication ?

  7. #7
    Join Date
    Nov 2001
    Posts
    335
    Quote Originally Posted by davey23uk
    my god - what a load of rubbish, you cannot set transation like that!! all you are doing is supressing the error it gives

    gee whizz

    It does remove pending transaction even when commit or rollback force do not work .try it for yourself before calling it 'load of rubbish"

    Under certain conditions, it may not be possible for an automatic recovery to execute without errors. As a result, recovery cannot identify it. (A possible symptom for this situation is when the ORA-02062 error is raised.) In this case, the DBA may use the DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY procedure to clean up any entries whose state is not "prepared."
    Last edited by BV1963; 07-09-2008 at 10:47 AM.
    One, who thinks that the other one who thinks that know and does not know, does not know either!

  8. #8
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    Quote Originally Posted by BV1963
    It does remove pending transaction even when commit or rollback force do not work .try it for yourself before calling it 'load of rubbish"
    Perhaps changing the process would stop this error from occurring. This just looks like a kludge to keep bad code running.

  9. #9
    Join Date
    Aug 2007
    Location
    Cyberjaya,kuala lumpur
    Posts
    340
    Quote Originally Posted by gandolf989
    Perhaps changing the process would stop this error from occurring. This just looks like a kludge to keep bad code running.
    needs some alteration in the code

  10. #10
    Join Date
    Aug 2007
    Location
    Cyberjaya,kuala lumpur
    Posts
    340
    please let me know why the transaction is pending,how to find where it is blocking.How to check which query is performing when the state of transaction
    is prepared

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