other option available : alter database rename global_name XXXSee What Oracle Said :Database Links: Troubleshooting the ORA 2085 "database link %s connects to %s" When the source database initialization parameter GLOBAL_NAMES is set to true, the database link name must match the target database global name as it exists in the GLOBAL_NAMEview in the data dictionary. The GLOBAL_NAME can be determined by logging in to the database with system privileges and issuing the following command: SQL>Select * from global_name;Additionally, if you do not specify the domain portion of the dblink name in the create statement, Oracle automatically qualifies the link name with the domain of the SOURCE database global name view.Check the contents of ALL_DB_LINKS for the fully qualified link name.For example, if you defined a database link in PROD.ORACLE.COM to connect to target instance TEST.WORLD.COM in the following manner:SQL>Create public database link TEST connect to userid identified by password using ?test?;SQL>select * from tablename@TEST;This select would yield the following error:ORA-2085 "database link TEST.ORACLE.COM connects to TEST.WORLD.COM"The correct syntax for defining the link would be:SQL>Create public database link TEST.WORLD.COM connect to userid identified by password using ?test?;SQL>select * from [email protected];Would yield desired result.It is possible to alter the GLOBAL_NAME table so that the domain portion of both SOURCE and TARGET global names are identical. This would eliminate the need to include the domain in the create database link statement.In the above example, we could alter the GLOBAL_NAME of TEST.WORLD.COM in the following manner:Login to TEST with system privileges and issue:SQL>alter database rename global_name to TEST.ORACLE.COM;Now, the create database link statement could also be changed.Login to PROD.SQL>create public database link TEST connect to userid identified by password using ?test?;A database link would be defined in ALL_DB_LINKS as TEST.ORACLE.COM.SQL>select * from tablename@TEST;This would yield the desired result.光说不练之龙飞虎
database link name must match the target database global name as it exists in the GLOBAL_NAMEview in the data dictionary.
The GLOBAL_NAME can be determined by logging in to the database with system privileges and issuing the following command: SQL>Select * from global_name;Additionally, if you do not specify the domain portion of the dblink name in the create statement, Oracle automatically qualifies the link name with the domain of the SOURCE database global name view.Check the contents of ALL_DB_LINKS for the fully qualified link name.For example, if you defined a database link in PROD.ORACLE.COM to connect to target instance TEST.WORLD.COM in the following manner:SQL>Create public database link TEST connect to userid identified by password using ?test?;SQL>select * from tablename@TEST;This select would yield the following error:ORA-2085 "database link TEST.ORACLE.COM connects to TEST.WORLD.COM"The correct syntax for defining the link would be:SQL>Create public database link TEST.WORLD.COM connect to userid identified by password using ?test?;SQL>select * from [email protected];Would yield desired result.It is possible to alter the GLOBAL_NAME table so that the domain portion of both SOURCE and TARGET global names are identical. This would eliminate the need to include the domain in the create database link statement.In the above example, we could alter the GLOBAL_NAME of TEST.WORLD.COM in the following manner:Login to TEST with system privileges and issue:SQL>alter database rename global_name to TEST.ORACLE.COM;Now, the create database link statement could also be changed.Login to PROD.SQL>create public database link TEST connect to userid identified by password using ?test?;A database link would be defined in ALL_DB_LINKS as TEST.ORACLE.COM.SQL>select * from tablename@TEST;This would yield the desired result.光说不练之龙飞虎