TRANSAZIONI DISTRIBUITE - DBA_2PC_PENDING

To View the pending transactions:

SELECT LOCAL_TRAN_ID, GLOBAL_TRAN_ID, STATE, MIXED, COMMIT#
FROM DBA_2PC_PENDING;

SQL> select 'rollback force '''||local_tran_id||''';' from DBA_2PC_PENDING where state='prepared';

SQL> rollback force '10.26.7211';


The actual transaction entry view is X$KTUXE 
([K]ernel [T]ransaction [U]ndo Transa[X]tion [E]ntry)
where the columns correspond to the following sections of the transaction id:


select
ktuxeusn,
ktuxeslt,
ktuxesqn, /* transaction id */
ktuxesta status,
ktuxecfl flags
from
x$ktuxe
where ktuxeusn= 10 and ktuxeslt= 26;--- and ktuxesqn=7211;

Solution 1 to Condition 1:

se una riga della DBA_2PC_PENDING non è in x$ktuxe e lo state of the transaction (in DBA_2PC_PENDING) è committed, rollback forced or commit forced then it can be cleaned by:

SQL>  select 'EXECUTE DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY( ''' ||local_tran_id||''');' 

 from DBA_2PC_PENDING ;

SQL> EXECUTE DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY( '10.26.7211');


Solution 2 to Condition 1: If the state of the transaction is "prepared", we have to clean manually as follows:

SQL> DELETE FROM SYS.PENDING_TRANS$ WHERE LOCAL_TRAN_ID = '1.10.6860';
SQL> DELETE FROM SYS.PENDING_SESSIONS$ WHERE LOCAL_TRAN_ID ='96.22.163456' ;
SQL> DELETE FROM SYS.PENDING_SUB_SESSIONS$ WHERE LOCAL_TRAN_ID = '96.22.163456';
SQL> COMMIT;


Connessioni riferite a transazioni pending in entrata o in uscita dal db

select * from dba_2pc_neighbors;