Home » RDBMS Server » Server Administration » ORA-02042: too many distributed transactions
ORA-02042: too many distributed transactions [message #187225] Fri, 11 August 2006 06:45 Go to next message
prashanth_gs
Messages: 67
Registered: November 2005
Location: chennai
Member
Hi all,

We are getting error

ORA-02042: too many distributed transactions when I select from a view from dblink.

There are some 17000 pending transactions in dba_2pc_pending.

As suggested while searching in google we increased the distributed_transactions parameter to 5000. then it works,
After crossing the 5000 again its failed, so again now we increased to 10000. I think within some time it will fail.

Note: There is not much transactions going on, I don't know how there are so many transactions in dba_2pc_pending table.

Db: Oracle 8i
OS: SunOS 5.8

Please help us to fix the issue ASAP.

Thanks

Prashanth
Re: ORA-02042: too many distributed transactions [message #187241 is a reply to message #187225] Fri, 11 August 2006 07:49 Go to previous messageGo to next message
Frank Naude
Messages: 4502
Registered: April 1998
Senior Member
You may have a very unstable network. Ask your networking team to investigate and if possible upgrade your network capacity.

02042, 00000, "too many distributed transactions"
// *Cause: the distributed transaction table is full, because too many
//      distributed transactions are active.
// *Action: increase the INIT.ORA "distributed_transactions" or 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.
Re: ORA-02042: too many distributed transactions [message #187248 is a reply to message #187241] Fri, 11 August 2006 08:53 Go to previous message
prashanth_gs
Messages: 67
Registered: November 2005
Location: chennai
Member
okay.

In the mean time we found that many transactions are failing.

If i rollback them which are failing back so that the no. of distributed transactions will get reduced right!!

can i follow these steps:

1. select DBUSER_OWNER, LOCAL_TRAN_ID from dba_2pc_neighbors where DBUSER_OWNER='INTERFACE'; (INTERFACE is schema which fails)

2. exec DBMS_TRANSACTION.ROLLBACK_FORCE (&xid );

xid= Local transaction id.

If this works?

Please advice.

Thanks.

Previous Topic: Is my database still rolling back?
Next Topic: Status BLOCKED in Listener
Goto Forum:
  


Current Time: Wed Dec 07 02:58:00 CST 2016

Total time taken to generate the page: 0.17554 seconds