Skip to Main Content

Database Software

Cleaning up 2phase commit transactions

Geert GruwezJan 26 2016

Idea: Be able to provide privileges to a normal db user so they can cleanup pending 2pc transactions.

Sometimes I need to cleanup lost transactions found in dba_2pc_pending.

This can only be done with the sys user.

It would help if privileges can be assigned to a normal user so they can initiate cleanup.

Technically ... cleaning up those transactions here is dead simple.

Run this script, and then it's output in sqlplus

select 'rollback force '''||local_tran_id||'''; '||chr(10)||

'exec dbms_transaction.purge_lost_db_entry('''||local_tran_id||'''); '||chr(10)||

'commit; '||chr(10)||chr(10) t 

from dba_2pc_pending;

Or is this cleanup supposed to work "as designed" ?

Meaning ... you have to give the sys password to anybody wanting to cleanup ?

An automated cleanup job "with the sys user" is a solution.

However, not a lot of people will bother investigating the reasons for failures if it's all automated ...

I know it's against policy ... but even dba's take a holiday !

Post Details
Added on Jan 26 2016