Oracle-L: RE: Debugging ORA-2042 error

From: Deshpande, Kirti <kirti.deshpande_at_verizon.com>
Date: Fri, 06 Dec 2002 08:24:30 -0800
Message-ID: <F001.00514794.20021206082430@fatcity.com>

Take a look at this article : http://www.jlcomp.demon.co.uk/faq/find_dist.html and the link in there to Tom Kyte's article.

Kirti

-----Original Message-----

Sent: Friday, December 06, 2002 9:34 AM
To: Multiple recipients of list ORACLE-L

That's right. How can I find out which transaction is distributed and which isn't? And how do I kill it?

The scenario would be:

I log on to the db with the DBA account.
Query on v$transaction to get the list of transactions and find out who is running these from v$session. (What would be the query?)
Find out which of these transactions are distributed (query??)
Kill some such transactions depending on who is running them (How?)

Had a quick look at the distributed databases guide but it doesn't mention anything on any of the above tasks.

Thanks & regards,
Charu Joshi

-----Original Message-----

Kirti
Sent: Friday, December 06, 2002 2:40 PM
To: Multiple recipients of list ORACLE-L

A simple 'select' (non-DML) from a remote database is also treated as a transaction by the source database (run a simple test to count from v$transaction (on source) after issuing a 'select' that refers a remote object). One can either exit the session or issue a commit/rollback, after 'select' is done to terminate such a transaction. If that is not possible, then raising the value of the init parameter (or killing transactions) is the only choice.

Kirti

-----Original Message-----

Sent: Friday, December 06, 2002 6:29 AM
To: Multiple recipients of list ORACLE-L

Dear Listers,

Oracle 8.1.7 on HP-UX11

We have three databases, one master and two slaves. On the slaves resides a view which queries data from master using a database link.

While querying from the view, we often get the error 'ORA-02042: too many distributed transactions'.

We are pretty sure that the error is raised on slaves and not on the master.

I found out that the 'distributed_transactions' parameter is set to a very low value, which is causing this error in the first place. But due to various reasons, this will not be possible soon.

The only thing I can think of is to have a list of all the distributed transactions, check which ones can be killed and kill them.

I tried the dba_2pc_pending table, but it doesn't have any records; still I am getting the ORA-02042 error.

Which are the views that would give me more information about the distributed transactions?

I would really appreciate if you can point out some document or article which treats this in detail.

Thanks & regards,
Charu

PS: How to check the archives of this list? I logged on to www.orafaq.com and searched on 'ORA-02042', without any good hits. I have lost the mail received at the time of joining the list.

Disclaimer

This message (including any attachments) contains confidential information intended for a specific individual and purpose, and is protected by law. If you are not the intended recipient, you should delete this message and are hereby notified that any disclosure, copying, or distribution of this message, or the taking of any action based on it, is strictly prohibited.

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Deshpande, Kirti
INET: kirti.deshpande_at_verizon.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services


---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message

to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).

Disclaimer

Visit us at http://www.mahindrabt.com

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Deshpande, Kirti
INET: kirti.deshpande_at_verizon.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services


---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message