Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Database Link & ORA-02042
Hello folks
Can anybody explain what's going on here?
I have two databases: cats (8.1.6.0.0 on NT Server 4 SP6) and eco (8.0.5.2.0 on HP-UX 11.0 (various patches)). I need to select data in eco from cats, but only for read purposes, ie I'm not trying to do any updates.
I have added a non-public database link in cats to point to eco, with a fixed username and password. The user name in the link is valid on the eco database, and the tnsnames.ora is happy with the link from the NT server to the eco database, ie I can log into eco and select data in SQL*PLus on the NT server with this username and password.
At first I find everything works fine and I can select the appropriate data into cats from eco. After a couple of weeks, however, I find I keep getting:
ORA-02042: too many distributed transactions
where previously the data was coming across fine. The reason for this is that
the database link is not working any more. Upon testing the database link I
find that I get the message "Database link is not active" in Enterprise
Manager, even though the remote database is up and running and I can (still)
log into it with SQL*PLus. There is no (obvious) way to get the link
"active"
again.
The GLOBAL_NAMES parameter is set to FALSE on both databases, and in the init.ora files.
This has happened in the past before, and in the end the only way I can get
it
working is to delete the link, bounce the database, recreate it and then wait
for it to fail again, which is somewhat inconvenient as you can imagine!
Simply deleting the link and recreating is not sufficient to get it working
again.
Why is this happening?
Thanks
Tarik