Put your tips and tricks online - Share your knowledge! Login | Register
 
 
  Search     Advanced search
 

Home | Ask Question | Add tip | Questions | My tips | Recent tips & tricks | Suggest a category | FAQ | Forums

 
 
 
 Category : Home > Databases > Oracle     

How to remove pending distributed transactions


sqlplus '/ as sysdba'


select count(*) from DBA_2PC_PENDING ;

  COUNT(*)
----------
        22


select max(fail_time) from dba_2pc_pending;

MAX(FAIL_
---------
03-MAR-12


select 'rollback force '''||local_tran_id||''' ;' from dba_2pc_pending ;

'ROLLBACKFORCE'''||LOCAL_TRAN_ID||''';'
--------------------------------------------------------------------------------------
rollback force '15.41.373682' ;
rollback force '18.10.103548' ;
rollback force '20.35.447783' ;
...


select 'exec dbms_transaction.purge_lost_db_entry('''||local_tran_id||''' )' , 'commit;' from
dba_2pc_pending
'EXECDBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('''||LOCAL_TRAN_ID||''')'
----------------------------------------------------------------------------------------------------
'COMMIT;'
--------------------------------
exec dbms_transaction.purge_lost_db_entry('15.41.373682' )
commit;

exec dbms_transaction.purge_lost_db_entry('18.10.103548' )
commit;

exec dbms_transaction.purge_lost_db_entry('20.35.447783' )
commit;
...


Run the generated script.


select count(*) from DBA_2PC_PENDING ;

  COUNT(*)
----------
         0


  Options
 
   del.icio.us  |  newsvine  |  digg  |  furl  |  google  |  yahoo  |  Ma.gnolia  |  vigillar  |  reddit  |  technorati  |  icerocket  |  pubsub

     (Average: 5 / Votes: 4)   Rate this tip:    

Start discussion or add comment to this tip

  Details
Tip reference : #291
views : 9530
Added on : 06/28/12
Submited by : h8dk97
 
Send a message Send a message Printer friendly output Printer friendly output
Display this member's tips Display this member's tips (219)
 
 
<< Previous Next >>
 Most viewed tips 
  Databases > Oracle > Security : How to unlock Oracle user account  
  Databases > Oracle > Performance Tuning : How to enable trace in Oracle  
  Databases > Oracle > DR : Adding new datafiles to standby database  
  Operating Systems > Unix : How to kill Unix user session  
   
  All categories
Databases | Programming | Hardware | Operating Systems | Networking | Internet | ERP / CRM | Games & Multimedia | Graphics & Design | Miscellaneous | Mobile Phones / Tablets | Office Software
 
 

Home |  FAQ |  Terms of Use |  Privacy Policy

© 2005 tiplib.com