ITPub博客

首页 > Linux操作系统 > Linux操作系统 > oracle db link 与 global_names 参数关系

oracle db link 与 global_names 参数关系

原创 Linux操作系统 作者:is.x 时间:2011-07-02 23:56:27 0 删除 编辑

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

IP192.168.100.162

ORACLE_SIDHX

 

Machine 2

IP192.168.100.4

ORACLE_SIDprepaid

 

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 1tnsnames.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 1tnsnames.ora文件

修改prepaid_testprepaid

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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2011-04-27

  • 博文量
    73
  • 访问量
    265336