oracle db link 与 global_names 参数关系

在oracle参数中，有一个参数叫global_names，如果该参数为TRUE，那么在使用db link时，db link的名字一定要和被访数据库实例名一致，否则会报ORA-2085错。

 

以下是10.2版本下该参数的说明：

 

SQL> select * from v$version;

 

BANNER

----------------------------------------------------------------

Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod

PL/SQL Release 10.2.0.3.0 - Production

CORE    10.2.0.3.0      Production

TNS for Solaris: Version 10.2.0.3.0 - Production

NLSRTL Version 10.2.0.3.0 - Production

 

SQL>

SQL>

SQL> select name,description from v$parameter where name='global_names';

 

NAME                 DESCRIPTION

-------------------- ----------------------------------------------------------------------------------------------------

global_names         enforce that database links have same name as remote database

 

Machine 1：

IP：192.168.100.162

ORACLE_SID：HX

 

Machine 2：

IP：192.168.100.4

ORACLE_SID：prepaid

 

1．在Machine 2的数据库中增加用户

以oracle用户登录Machine 2

$export ORACLE_SID=prepaid

$sqlplus / as sysdba

SQL> create user test identified by test;

User created.

SQL> grant connect,resource to test;

Grant succeeded.

 

2．修改Machine 1的tnsnames.ora文件

增加如下内容：

prepaid_test=

(DESCRIPTION =

  (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.4)(PORT = 1521))

  (CONNECT_DATA =

    (SERVER = DEDICATED)

    (SID = prepaid)

  )

)

 

3．创建db link

以oracle用户登录Machine 1

$export ORACLE_SID=HX

$sqlplus / as sysdba

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

Database link created.

SQL> show parameter global_names

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

global_names                         boolean     TRUE

SQL> select * from dual@prepaid_test;

select * from dual@prepaid_test

                   *

ERROR at line 1:

ORA-02085: database link PREPAID_TEST connects to PREPAID

 

SQL> alter system set global_names=false;

System altered.

SQL> select * from dual@prepaid_test;

 

D

-

X

 

 

可以看到，当global_names=false时，db link可以连接远程数据库。

$oerr ora 2085

02085, 00000, "database link %s connects to %s"

// *Cause: a database link connected to a database with a different name.

//  The connection is rejected.

// *Action: create a database link with the same name as the database it

//  connects to, or set global_names=false.

//

 

4．修改Machine 1的tnsnames.ora文件

修改prepaid_test为prepaid：

prepaid =

(DESCRIPTION =

  (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.4)(PORT = 1521))

  (CONNECT_DATA =

    (SERVER = DEDICATED)

    (SID = prepaid)

  )

)

 

5．重新创建db link

以oracle用户登录Machine 1

$export ORACLE_SID=HX

$sqlplus / as sysdba

SQL> drop public database link prepaid_test;

Database link dropped.

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

Database link created.

SQL> alter system set global_names=true;

System altered.

SQL> show parameter global_names

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

global_names                         boolean     TRUE

SQL> select * from dual@prepaid;

 

D

-

X

 

 

可以看到，当global_names=true时，如果创建的db link名字与远程数据库的实例名一致，还是可以访问的。

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/20750200/viewspace-701217/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/20750200/viewspace-701217/