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 :)

About these ads

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 Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Theme: Silver is the New Black. Get a free blog at WordPress.com

Follow

Get every new post delivered to your Inbox.

Join 203 other followers

%d bloggers like this: