Oracle GLOBAL_NAMES参数测试

windjack

Oracle数据库Global_names参数测试

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

Oracle数据库global_names参数设置为TRUE时，使用database link时，database link的名称必须与被连接库的global_names一致。

下面做一个测试，创建数据库链接的库即当前库为orcl(Centos7.8  oracle19c)，被连接的库即远程库为orcl_win(win10  oracle12c)

以下的测试全部在Centos中的库orcl上执行

在orcl中设置TNS连接配置：

orcl_win =

  (DESCRIPTION =

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

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = orcl_win)

    )

  )

显示当前设置：

[oracle@dbserver admin]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jun 10 17:12:19 2020

Version 19.3.0.0.0  

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

SQL> show parameter global_names

NAME                                 TYPE        VALUE

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

global_names                         boolean     FALSE

创建DATABASE LINK

SQL> create public database link dblink_win using 'orcl_win';

测试连接：

SQL> conn hr/hr

Connected.

SQL> select count(*) from employees@dblink_win;

  COUNT(*)

----------

       107

修改global_names参数为true

SQL> alter system set global_names=true;

System altered.

再测试连接：

SQL> conn hr/hr

Connected.

SQL> select count(*) from employees@dblink_win;

select count(*) from employees@dblink_win                               *

ERROR at line 1:

ORA-02085: database link DBLINK_WIN connects to ORCL

证明要通过DBLINK连接到其它库时，当前库GLOBAL_names不能为为true，

远程服务器的GLOBAL_names设置没要求。

再修改global_names参数为false

SQL> alter system set global_names=false;

System altered.

测试连接：

SQL> conn hr/hr

Connected.

SQL> select count(*) from employees@dblink_win;

  COUNT(*)

----------

       107

测试其它用户连接去查询orcl_win上HR的表：

SQL> conn test/test

Connected.

SQL> select count(*) from hr.emp@dblink_win;

select count(*) from hr.emp@dblink_win

                            *

ERROR at line 1:

ORA-01017:  

ORA-02063: preceding line from DBLINK_WIN

证明要通过DBLINK查询远程服务器的表，当前登录的用户和相关权限必须在远程服务器上也要存在。

windjack

附上062题一道

QUESTION 214
Examine the parameter settings in your local ORCL database:
DB_DOMAIN=hq.example.com
DB_NAME=orcl
REMOTE_OS_AUTHENT=TRUE
GLOBAL_NAMES=TRUE

Examine the parameter settings for the remote SALES database:
DB_DOMAIN=hq.example.com
DB_NAME=sales
REMOTE_OS_AUTHENT=TRUE
GLOBAL_NAMES=true

As the SYS user, you issue the following command on ORCL:
CREATE PUBLIC DATABASE LINK sales.us.example.com USING ‘sales1’;
The sales1 tnsnames entry points to the sales database instance.

Which two are requirements to ensure that you can successfully connect by using this database link?
A.        The GLOBAL_NAMES initialization parameter should be set to FALSE in the ORCL database.
B.        The SALES1 net service name should be known to both the local and remote databases.
C.        Local database users who use this database link should have accounts and the required privileges in the remote database.
D.        The REMOTE_OS_AUTHENT initialization parameter should be set to FALSE in the remote database.
E.        Only the user creating the database link must have an account and the required privileges in the remote database.
Answer: AC