- 17.9K All Categories
- 3.4K Industry Applications
- 3.4K Intelligent Advisor
- 73 Insurance
- 537.2K On-Premises Infrastructure
- 138.6K Analytics Software
- 38.6K Application Development Software
- 6K Cloud Platform
- 109.6K Database Software
- 17.6K Enterprise Manager
- 8.8K Hardware
- 71.2K Infrastructure Software
- 105.3K Integration
- 41.6K Security Software
Cleaning up 2phase commit transactions
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 !