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 !