Create database link ORA-2085

[sambavan]

Do you have your global_name turned off? What is the error message you are getting, post it including the error number....

Sam

[wwalice]

I am sure you need change tnsnames to supply the "SERVICE_NAMES" even if currently you can tnsping ***.

[goodhealth]

Change global_names to false does the trick! However I should be able to name my link the same as SID with global_name equal true. Global_name basically forces your link name to be the same as your db or sid. Follow this rule should work also.

[goodhealth]

I reset global_name to true and it worked. However when I use DESC on a remote table: for exp; DESC PS_TAX@REMOTE_DB.WORLD, it came backup invalid connection string message. However doing SELECT on a remote table works fine. Why didn't DESC work, it used to work.

[julian]

Originally posted by goodhealth
Change global_names to false does the trick! However I should be able to name my link the same as SID with global_name equal true. Global_name basically forces your link name to be the same as your db or sid. Follow this rule should work also.

In OPS, you cannot name the DB link the same as the DB, for you have at leas 2 instances. Metalink has some wrong info on creating DB links in an Oracle Paralell Server enviroment in 8i. I remember it took me hours to figure out how to create my DB links :-)

[e067450]

See if this helps you ?

Found this in a Metalink article (DocID=1024124.6)

This is not documented in the Oracle manuals and can be useful (even required) when the init.ora parameter GLOBAL_NAMES is set to TRUE.....
Note that if GLOBAL_NAMES is set to FALSE, you can name the dblink anything you desire.

Database Links
--------------
Database links are created using the following syntax:

SQL> create database link
connect to identified by
using '';

The name of the database link should match the global name of the target
database if GLOBAL_NAMES=TRUE. This may seem restricting since then there can
be only one database link per schema to a given database if global_names is
set to true. To overcome this use database link qualifiers. For example:

SQL> create database link oradb@link1
using 'D:BOSTON-MFG';

NOTE: in this example, 'link1' is database link qualifier
and 'D:BOSTON-MFG' is the connect string

My NOTE : your select to the remote database would look like this :
select * from table_name@oradb@link1;

-Tom