How To Eliminate Pending Transactions Between Databases

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

 

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s