Coskan’s Approach to Oracle

June 26, 2007

ORA-02067: transaction or savepoint rollback required

Filed under: Tips — coskan @ 8:09 am

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 🙂

1 Comment »

  1. 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


RSS feed for comments on this post. TrackBack URI

Leave a comment

Blog at WordPress.com.