Thursday, November 02, 2006

About database link

To know the settings of the database to which the dblink belongs,
issue the following statements:

SQL> select owner,db_link from dba_db_links;

OWNER DB_LINK
-------------------- --------------------
SYS DB1.WORLD <=====This column shows the dblink name

SQL> select * from global_name;

GLOBAL_NAME
-----------------------------------------
XXX.YYY <=====Here is the current database global name in "[DB_NAME](.[DB_DOMAIN])" format,
and the ".[DB_DOMAIN]" may be empty.

If ".[DB_DOMAIN]" in global_name matches the ".world" in dblink name,
the ".world" can be omitted when using this dblink.
So the reason why a dblink which can be used without ".world" in the past must be used in full format now
may be that someone has changed the database global name to XXX.YYY and ".YYY" doesn't match ".world".

To fix this problem:

1.Verify the GLOBAL_NAME matches the "DB_NAME.WORLD".
To check the current value, issue the following commands using SQL*Plus:

SQL> SHOW PARAMETER DB_NAME
SQL> SELECT * FROM GLOBAL_NAME;

2.Issue the following statement using SQL*Plus:

SQL> ALTER DATABASE RENAME GLOBAL_NAME TO db_name.world;
----------------------------------------------------------------
Note: Contact your DBA before changing the global_name,
because someone may have changed this for certain purpose.
----------------------------------------------------------------

Reference:
MetaLink#117759.1 DATABASE LINK (DBLINK) TROUBLESHOOTING
KROWN#85279 GLOBAL_NAME をドメインなしの名前に変更できない

No comments: