Home » SQL & PL/SQL » SQL & PL/SQL » ORA-02046: distributed transaction already begun
ORA-02046: distributed transaction already begun [message #204906] Wed, 22 November 2006 09:51 Go to next message
iannelis
Messages: 10
Registered: May 2006
Junior Member
Hi!

I have a serious problem in my j2ee application. The description of the situation :

1)There is a PL/SQL procedure in the local database which calls a remote procedure (which is in a different database). The remote procedure then updates certain tables in that remote database.
2)In most cases it all works just fine. But after some time i keep getting ORA-02046 errors in the logfile.

Is it somehow related to the fact that Oracle uses two phase committing?
Re: ORA-02046: distributed transaction already begun [message #204909 is a reply to message #204906] Wed, 22 November 2006 10:01 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Test of the error message is:
Quote:
ORA-02046 distributed transaction already begun

Cause: This is an internal error not normally encountered. A server session received a BEGIN_TRAN remote procedure call before finishing with a previous distributed transaction.

Action: Contact Oracle Support Services.


, so hie thee to Metalink.
Re: ORA-02046: distributed transaction already begun [message #204912 is a reply to message #204909] Wed, 22 November 2006 10:06 Go to previous messageGo to next message
iannelis
Messages: 10
Registered: May 2006
Junior Member
that was'nt too helpful. I have searched answers from metalink- nothing..
Re: ORA-02046: distributed transaction already begun [message #204920 is a reply to message #204912] Wed, 22 November 2006 10:35 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Then raise a SR with them.
If this is a production system, you should get a pretty prompt answer.

I found this on the web

URL
Quote:
check dba_2pc_pending, there may be entries in there.

Oracle follows the 2-phase commit method when queries are submitted from
stored programming units, it is default behaviour that cannot be changed.

We ran into this with a Web application that was doing remote querying, and
Oracle said the only workaround for queries is issue a set transaction read
only before the query is submitted.



Other than that, what exactly are you expecting from us?
The text
Quote:
This is an internal error not normally encountered
means that something's gone wrong in the blue tubey bits inside the database.
The text
Quote:
Action: Contact Oracle Support Services.

means just what it says.
Re: ORA-02046: distributed transaction already begun [message #204922 is a reply to message #204920] Wed, 22 November 2006 10:42 Go to previous messageGo to next message
iannelis
Messages: 10
Registered: May 2006
Junior Member
i am trying to find a solution. The error message says what it says.
Re: ORA-02046: distributed transaction already begun [message #204939 is a reply to message #204922] Wed, 22 November 2006 13:42 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
iannelis wrote on Wed, 22 November 2006 17:42
i am trying to find a solution. The error message says what it says.

Indeed it does:
Action: Contact Oracle Support Services.

Re: ORA-02046: distributed transaction already begun [message #216032 is a reply to message #204906] Wed, 24 January 2007 14:43 Go to previous messageGo to next message
slc2
Messages: 1
Registered: January 2007
Junior Member
Actually, quite a few people have sent this error into Oracle, and there are a number of cases on Metalink about this error, none of them provide much elucidation. So actually, insisting he contact Oracle might not actually do too much good. If so, being able to provide them with a trace file may help. This is an error that often doesn't throw any alerts.

Basically, what has happened is that the specific server session has received a begin_tran rpc BEFORE it has finished with a previous distributed transaction.

There is no real clear, set pattern to the conditions that create this error message, and in many cases, there doesn't seem to be a workaround either.

In one case, the person's application was in autocommit mode and turning that off resolved the issue. In one case, the error occured in SQLPlus, but not when running the same code in OWB (where the code had been created). In another case, a person said they had increased DISTRIBUTED_TRANSACTION parameter and had it stop. In another case, the problem occured in a jdbc application, but not when the same code was run from SQLPlus.

It has been associated with select statements going from a 9.2.x database to an 8.1.x database. In most cases, there is a dblink involved, one way or another (buried in a view or somehow). Some reports asscoiate it with insert statements.

In my case, I have been told to write a pl/sql procedure to replace Oracle's multimaster replication. As expected it does an update on a table on a remote database. This update statement causes the error to occur. The earlier version of this procedure, which is as similar as possible except that it does not use bulk collect to gather the data, does not appear to cause the error to occur.

This is a difficult error and not alot of information available from metalink. It seems to occur right in the middle of a distributed transaction.

[Updated on: Wed, 24 January 2007 14:53]

Report message to a moderator

Re: ORA-02046: distributed transaction already begun [message #218641 is a reply to message #216032] Fri, 09 February 2007 02:10 Go to previous messageGo to next message
xuberant02
Messages: 1
Registered: February 2007
Junior Member
Hi,

I am facing the following error. ORA-02046: distributed transaction already begun. Can any1 help me to resolve this issue ?

Kind Regards
Re: ORA-02046: distributed transaction already begun [message #355140 is a reply to message #204906] Wed, 22 October 2008 17:14 Go to previous message
lodeghost
Messages: 1
Registered: October 2008
Junior Member
I struggled with this error for 2 days. It would happen in Reporting Services but not in Toad. Plus, it would only happen the second time I ran the report in SSRS. The first time would work well.

Finally I traced the problem to one particular table in my stored proc that was retrieved across two linked servers. Remove the table and the problem went away.

My workaround:
1) Find the problem table (likely one retrieved via dblink).
2) In your stored proc, start by making a copy of the table on the server you are logged into in a temp table.
3) Use the non-linked copy of the table instead of the one you dblinked to in the query part of the stored proc.

Not elegant nor will it work for everyone, but hope it helps.

Smile
Previous Topic: Union All
Next Topic: Analyzing within a block
Goto Forum:
  


Current Time: Sun Dec 04 20:54:18 CST 2016

Total time taken to generate the page: 0.13152 seconds