I saw this error on logfile of an archiving batch job . The batch job works on production database and it inserts the row to the archive database over database link and after insertion it deletes the row from the production database . When I looked at the error definition I got confused ??? The error definition says
ORA-02067: | transaction or savepoint rollback required |
Cause: | A failure (typically a trigger or stored procedure with multiple remote updates) occurred such that the all-or-nothing execution of a previous Oracle call cannot be guaranteed. |
Action: | rollback to a previous savepoint or rollback the transaction and resubmit. |
There wasn’t a trigger on the remote database table and this archiving batch was working every weekend, without any exception. There must be something new and weird. After googling I found the reason on forums.oracle.com thread. It was a transaction of an inactive session which was on the archiving table on the remote database. I killled the session, restarted the batch and went to get a pint of ale 🙂
How to identify the failded/locked transaction id in the remote database? Is there any query? How to rollback the transaction id?
Comment by Arivu — January 23, 2013 @ 4:30 pm