Ora-00164: Autonomous and Distributed transactions
Frank Piron
we encounter an ora-00164 in a procedure
using an autonomous transaction.
RDBMS: Oracle 8.1.7.4
OS: Solaris 2.8 on an ULTRA Sparc (64Bit)
I searched metalink and detected the reason
as a db-link to a financial database.
We only select tables of the remote db, no DML.
The issue is filed as a bug and resolved in
Oracle 9.2
Question: Is there a workaround for using autono-
mous transactions and read-only db-links in Oracle
8.1.7.4 (e.g. setting distributed_transactions = 0
in initSID.ora)?
--
Frank Piron,
defrankatkonaddot
(leftrotate two)
Mark D Powell
stated in the 8.1.7 documentation that anonymous transactions could not
be called as part of a distributed transaction. Oracle removed this
limitation in version 9.
The workaround is not to call the anonymous transaction as part of the
distributed transaction. In some cases the remote query can be issued
and then committed to end the transaction allowing a new transaction to
process the anonymous transaction. But what you can do depends on the
logical unit of work that you are dealing with and where the remote
query lies.
HTH -- Mark D Powell --
Frank Piron
> The ORA-00164 error in version 8.1.7 is not a bug. It was clearly
> stated in the 8.1.7 documentation that anonymous transactions could not
> be called as part of a distributed transaction. Oracle removed this
> limitation in version 9.
I should have been more clear:
The bug is that the poor *existence* of a db-link suffices to cause the
error.
No distributed transaction has to be involved!
This is Bug No:692232.
Mark D Powell
no hits. Are you sure that is the correct number?
We ran several versions of 8.1.7 and make regular constant use of
distributed transactions and also used anonymous transactions
successfully as long as we did not mix the two together. We were
mostly on 8.1.7.4. I was trying to see if the bug was platform
specific or fixed in one of the patch sets.
We still run an 8.1.7 db and I wanted to check it out to see if we have
a potential issue that I need to note. Fortunately we are mostly 9.2.
Frank Piron
Am 25 Feb 2005 06:38:41 -0800 schrieb Mark D Powell <Mark....@eds.com>:
> Frank, I searched the Oracle bug database for the number 692232 and got
> no hits. Are you sure that is the correct number?
Yes, i searched successfully in metalink right now.
> We ran several versions of 8.1.7 and make regular constant use of
> distributed transactions and also used anonymous transactions
> successfully as long as we did not mix the two together. We were
> mostly on 8.1.7.4. I was trying to see if the bug was platform
> specific or fixed in one of the patch sets.
Our customer is running 8.1.7.4 on Sun Solaris 2.8, Sparc(64Bit).
The bug was filed for Solaris 2.7, Sparc(32Bit).
Some people mailed the same Problem to metalink (Forum). The problem was
delegated to the OS Group.
> We still run an 8.1.7 db and I wanted to check it out to see if we have
> a potential issue that I need to note. Fortunately we are mostly 9.2.
I will test now setting distributed_transactions = 0 in initSID.ora
since we do not need them. May be this helps.
Thanks for reply!
Mark D Powell
when I say your update. (I had left bug unchecked and tech doc checked
instead). The bug report is not very clean since it states the problem
is calling an anonymous transaction from within a distributed
transaction and that a distributed timeout will not occur if a deadlock
occurs. It also lists a couple of 8.1.6 problems.
None of this will help me not did any of the related links look like
they might contain information that might help you. I think your idea
is one worth trying and I hope it works. I will keep the idea in mind.
Good Luck, Mark.