When work in environments that have many databases, or when we have within the same database environments Production, Quality/Test and Development, as recommended by ITIL, we usually create database links to access information from a database to another, of course ITIL recommends between environments haven’t links between Production and Test/Development and by security is not good idea to have these “tunnels”. The problem of working with trasacciones (ACID) distributed between databases is that it can sometimes fail, fails the network, one of the databases fall in mid transacion or anything else that makes no transaction can be completed. Oracle uses a method called double commit for such transactions, we will not see this method in this post. What if we are going to see is what happens with the transaction that is pending. Normally Oracle is able to solve it, sometimes it may take time, but there are other situations that fails to release them, the solution involves cancel it.
To perform the purge, run the following query to see which transactions are pending, this query should not return rows, it’s the best case. If it returned tuples we take the rows returned, it is the identifier of the transaction
select local_tran_id, state, tran_comment from dba_2pc_pending;
SELECT LOCAL_TRAN_ID FROM DBA_2PC_PENDING;
With LOCAL_TRAN_ID obtained, execute the package dbms_transaction by passing the LOCAL_TRAN_ID, as shown in the following example;
EXECUTE DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY (‘22.10.01973’);
If we run the above query should not return anything.
HTH – Antonio NAVARRO