Connect error and database links

From: Peter Robson <pgro_at_bgs.ac.uk>
Date: Thu, 22 Jul 2004 13:24:04 +0100
Message-ID: <343451396.20040722132404@bgs.ac.uk>

People -

I have a puzzling situation here, and would be grateful for any input.

We have two instances, geographically remote, both v8.1.7, and both connected using Net8.

Database links are created differently on both instances - as thus:

On instance DB2:

create database link Ora1 connect to FRED identified by xxxxxxx using 'DB1.WORLD';

and on instance DB1:

create database link Ora2 connect to FRED identified by xxxxxxxx using 'DB2';

DBlink 'Ora1' cannot be created using the form applied to the creation of DBlink 'Ora2'. The names are changed to protect the innocent, and no, we are NOT using DB2... :-)

These links work, data can be queried, tables described etc.

However, if while connected to instance DB2, and one then retrieves data from DB1 using the link, and then attempts to connect to DB1 as thus: SQL>connect fred_at_db1 - the following error appears:

Enter password: ******
ERROR:

   ORA-00604: error occurred at recursive SQL level 1
   ORA-04092: cannot COMMIT in a trigger
   ORA-06512: at line 16

ERROR:
ORA-24315: illegal attribute type

This error ONLY occurs on DB2.

If no use of the database link had been made during that session, a connection to DB1 would have been entirely successful. Further, it is not as though cause can be traced to a trigger on the remote table being queried, as the error appears even after querying a table with no triggers on it.

Can anyone advise on what we have overlooked here? I must admit that I have not spent much time in the TNS area, so could have overlooked something obvious. The text for the first error message (0604) ends with the ominous 'contact customer support' - is it time to do this?

thanks,

peter
edinburgh
--

mailto:pgro_at_bgs.ac.uk

This e-mail message, and any files transmitted with it, are confidential and intended solely for the use of the addressee. If this message was not addressed to you, you have received it in error and any copying, distribution or other use of any part of it is strictly prohibited. Any views or opinions presented are solely those of the sender and do not necessarily represent those of the British Geological Survey. The security of e-mail communication cannot be guaranteed and the BGS accepts no liability for claims arising as a result of the use of this medium to transmit messages from or to the

BGS. .                            http://www.bgs.ac.uk
*********************************************************************



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

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

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.
--

Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html

Received on Thu Jul 22 2004 - 07:20:52 CDT