SQL Query to Find Out Pending Distributed Transaction in Oracle Database

A distributed transaction includes one or more statements that, individually or as a group, update data on two or more distinct nodes of a distributed database. 

Dictionary Views: 

1. dba_2pc_neighbors 

2. dba_2pc_pending 

SQL 1.

Set pages 1000 lines 120   
 col LT_ID for a25  
 col GT_ID for a45  
 col STATE for a18  
 col Mixed for a6  
 col advice for a6  
 select  
   local_tran_id LT_ID,  
   global_tran_id GT_ID,  
   state ,mixed,advice  
 from   
   dba_2pc_pending  
 order   
   by local_tran_id;  

advertisements

 

SQL 2.

COL local_tran_id FORMAT a15
COL in_out FORMAT a10
COL DB FORMAT a30
COL user FORMAT a15
COL interface FORMAT a3
SELECT  local_tran_id LT_ID, 
 in_out, 
 database DB, 
 dbuser_owner user, 
 interface
FROM dba_2pc_neighbors
/