Re: database link name

From: DA Morgan <damorgan_at_psoug.org>
Date: Fri, 08 Dec 2006 08:10:14 -0800
Message-ID: <1165594201.898850@bubbleator.drizzle.com>

fitzjarrell_at_cox.net wrote:
> Charles Hooper wrote:

>> skyloon wrote:
>>> in oracle 9i, when I create database link, i put the name as CBSFAS,
>>> after save it, the name will become CBSFAS.US.ORACLE.COM.
>>>
>>> when select * from <table name>@CBSFAS, it returns error
>>> when select * from <table name>@CBSFAS.US.ORACLE.COM, it returns
>>> result, any body know why? how to get only CBSFAS as my database link
>>> name?
>> If you create the database link using SQLPlus rather than OEM, you
>> should be able to avoid this problem.  For assistance with the syntax,
>> see:
>> http://www.oracle.com/pls/db102/ranked?word=create+dblink
>>
>> Charles Hooper
>> PC Support Specialist
>> K&M Machine-Fabricating, Inc.

>
> If you have global_names set to true you get this behaviour through
> OEM, and even with SQL*Plus you'll get an error if you don't use the
> fully qualified tns alias as a link name. Setting global_names to
> false 'corrects' this situation.
>
>
> David Fitzjarrell

I'd be really pleased if you were correct. Alas that is no longer the case.

SQL*Plus: Release 10.2.0.2.0 - Production on Fri

Connected to:
Oracle Database 10g Enterprise Edition Release 1 With the Partitioning, OLAP and Data Mining opti

SQL> set linesize 121
SQL> col name format a40
SQL> col value format a40
SQL> SELECT name, value

2 FROM gv$parameter
3 WHERE name LIKE '%global%';

NAME                                     VALUE
---------------------------------------- -------
global_context_pool_size
global_names                             FALSE

SQL> CREATE DATABASE LINK local

2 CONNECT TO hr IDENTIFIED BY hr
3 USING 'local';

Database link created.

SQL> col name format a100
SQL> select name from link$;

NAME

CURR_USER.REGRESS.RDBMS.DEV.US.ORACLE.COM LOCAL.REGRESS.RDBMS.DEV.US.ORACLE.COM
TESTLINK.REGRESS.RDBMS.DEV.US.ORACLE.COM CONN_USER.REGRESS.RDBMS.DEV.US.ORACLE.COM SQL> SELECT db_link

2 FROM dba_db_links;

DB_LINK

CURR_USER.REGRESS.RDBMS.DEV.US.ORACLE.COM LOCAL.REGRESS.RDBMS.DEV.US.ORACLE.COM
TESTLINK.REGRESS.RDBMS.DEV.US.ORACLE.COM CONN_USER.REGRESS.RDBMS.DEV.US.ORACLE.COM SQL> I have no idea why they did this. But it breaks a lot of things.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Received on Fri Dec 08 2006 - 10:10:14 CST