June 26, 2007

ORA-02067: transaction or savepoint rollback required

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 🙂