Re: Error in oracle 8i - database links

avbpk_at_my-deja.com wrote:
>
> Dear Friends,
> I have installed oracle 8i on two different
> machines which have windows NT 4.0 workstation. I
> tried to create a database link so as to access
> data from the database in the neighbouring PC
> with the following command
>
> SQL> create database LINKNAME
> connect to system identified by manger
> using SERVICENAME
>
> After creating the link successfully, i tried to
> execute the following command through sqlplus
>
> SQL> select *from v$session_at_LINKNAME
>
> I am getting the following error message
> ora-02085: Database link LINKNAME connects to
> ORACLE
>
> Any suggestions to handle this error and why it
> is occuring
> Thanks in advance
> Kumar
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.

Basically the service name that you are using when you create the database link is not the same as the global name for the database you are connecting to, and by default your setup is using global naming rules. Note that if (as I suspect), the database you are connecting to across the DB Link has been created from the initial seed database provided on installation, the global name for this database is always ORACLE, and NOT the service name you gave it during installation (this is a bug in the install process and is fixed in 8.1.6, I believe).

So to check and fix, do the following.

1. Determine the correct service name you want, which should be the one that you use in the CREATE DB LINK - say, for this example, MYDB.WORLD
2. As SYS connected to this database, type

   select * from global_name;

   If my suspicion is correct, this will be ORACLE 3) To change, as sys connected to this database, type

   update global_name set global_name = 'MYDB.WORLD';    commit;
   select * from global_name;

   The global_name for the database should now be MYDB.WORLD. You may also want to repeat this exercise for the other half of the db link (with a different global name value)

4) Drop and recreate the database link as before, and it should work now.            

--
Regards,

Mark Townsend                         

Senior Product Manager				Ph: 	(650) 633 5764
Server Division					Fx: 	(650) 506 7222
Oracle Corporation				Email:	mtownsen_at_us.oracle.com