OraFAQ Forum: Server Administration » Distributed Transactions

Home » RDBMS Server » Server Administration » Distributed Transactions (Oracle 10g, Unix /Linux)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Distributed Transactions [message #657578]

Tue, 15 November 2016 01:58

virxiam
Messages: 2
Registered: November 2016

Junior Member

Hi All,

I'm trying to resolve 'ORA-02042: too many distributed transactions'.

Are there any tables/views I can use to list or count these distributed transactions?

Any help from you will be deeply appreciated. Thanks.

Kind regards,
/virxiam

Report message to a moderator

Re: Distributed Transactions [message #657579 is a reply to message #657578]

Tue, 15 November 2016 02:55

ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany

Senior Member

There seems to be one table mentioned in the dictionary:

SELECT * FROM DICTIONARY  
 WHERE Lower(comments) 
 LIKE '%distributed transactions%'

Report message to a moderator

Re: Distributed Transactions [message #657580 is a reply to message #657578]

Tue, 15 November 2016 03:29

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

ORA-02042: too many distributed transactions
 *Cause: the distributed transaction table was full because too many
        distributed transactions were active.
 *Action: Run fewer transactions.  If you are sure you don't have too
      many concurrent distributed transactions, this indicates an internal
      error and support should be notified.  Instance shutdown/restart
      would be a work-around.

There is no available catalog view for this but if you have SYSDBA access you can query X$K2GTE table.
For instance, from MIKA I open a transaction to MIKB2 and vice versa, the following gives the global transaction ids:

SYS> -- On MIKB2
SYS> select K2GTITID_ORA from X$K2GTE;
K2GTITID_ORA
---------------------------------------------
MIKA.myserver.ee9383b5.10.31.12438
MIKB2.myserver.df482311.3.20.42573

2 rows selected.

SYS> -- On MIKA
SYS> select K2GTITID_ORA from X$K2GTE;
K2GTITID_ORA
---------------------------------------------
MIKB2.myserver.df482311.3.20.42573
MIKA.myserver.ee9383b5.10.31.12438

2 rows selected.

Report message to a moderator

Re: Distributed Transactions [message #657581 is a reply to message #657580]

Tue, 15 November 2016 03:35

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Ooops, speaking too fast, you have V$GLOBAL_TRANSACTION (built on the table I mentioned), same queries:

SYS> select utl_raw.cast_to_varchar2(GLOBALID) gtxid from V$GLOBAL_TRANSACTION;
GTXID
-------------------------------------------------------------------------------
MIKA.myserver.ee9383b5.10.31.12438
MIKB2.myserver.df482311.3.20.42573

2 rows selected.

SYS> select utl_raw.cast_to_varchar2(GLOBALID) gtxid from V$GLOBAL_TRANSACTION;
GTXID
-------------------------------------------------------------------------------
MIKB2.myserver.df482311.3.20.42573
MIKA.myserver.ee9383b5.10.31.12438

2 rows selected.

[Updated on: Tue, 15 November 2016 03:36]

Report message to a moderator

Re: Distributed Transactions [message #657611 is a reply to message #657581]

Tue, 15 November 2016 18:52

virxiam
Messages: 2
Registered: November 2016

Junior Member

Thank you Thomas.
Thank you Michel.

I'm glad I have joined this awesome group!
Have an awesome day everybody! Razz

Report message to a moderator

Previous Topic:	Fatal NI connect error
Next Topic:	Java class invalid after upgrade from 10g to 11gr2

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Nov 28 16:38:40 CST 2024