Home » RDBMS Server » Server Administration » Distributed Transactions (Oracle 10g, Unix /Linux)
Distributed Transactions [message #657578] Tue, 15 November 2016 01:58 Go to next message
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
Re: Distributed Transactions [message #657579 is a reply to message #657578] Tue, 15 November 2016 02:55 Go to previous messageGo to next message
ThomasG
Messages: 3208
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%'
Re: Distributed Transactions [message #657580 is a reply to message #657578] Tue, 15 November 2016 03:29 Go to previous messageGo to next message
Michel Cadot
Messages: 65849
Registered: March 2007
Location: Nanterre, France, http://...
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.

Re: Distributed Transactions [message #657581 is a reply to message #657580] Tue, 15 November 2016 03:35 Go to previous messageGo to next message
Michel Cadot
Messages: 65849
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous message
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 Razz Razz
Previous Topic: Fatal NI connect error
Next Topic: Java class invalid after upgrade from 10g to 11gr2
Goto Forum:
  


Current Time: Sun Oct 21 22:37:34 CDT 2018