| ORA-02046: distributed transaction already begun [message #204906] |
Wed, 22 November 2006 09:51  |
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  |
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 #204920 is a reply to message #204912] |
Wed, 22 November 2006 10:35 |
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 #216032 is a reply to message #204906] |
Wed, 24 January 2007 14:43 |
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 #355140 is a reply to message #204906] |
Wed, 22 October 2008 17:14 |
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
