Oracle dblink介绍

最新推荐文章于 2023-08-10 14:44:08 发布

小麦苗DBA宝典

最新推荐文章于 2023-08-10 14:44:08 发布

阅读量639

点赞数

本文链接：https://blog.csdn.net/lihuarongaini/article/details/101298920

版权

Oracle dblink介绍

官网：https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_5005.htm

当用户要跨本地数据库访问另外一个数据库表中的数据时，本地数据库中必须创建了远程数据库的 DBLINK，通过 DBLINK 可以像访问本地数据库一样访问远程数据库表中的数据。其实， DBLINK 和数据库中的 VIEW 差不多，创建 DBLINK 的时候需要知道待读取数据库的 IP 地址、 ORACLE_SID 以及数据库用户名和密码。

在创建DBLINK之前，普通用户必须具有相关的权限才能创建DBLINK，以SYS用户登录到本地数据库可以看到相关的权限：

SELECT * FROM USER_SYS_PRIVS T WHERE T.PRIVILEGE LIKE UPPER('%LINK%');

SYS@PROD1> SELECT * FROM USER_SYS_PRIVS T WHERE T.PRIVILEGE LIKE UPPER('%LINK%');

USERNAME PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
SYS CREATE DATABASE LINK NO
SYS DROP PUBLIC DATABASE LINK NO
SYS CREATE PUBLIC DATABASE LINK NO

可以看出，在数据库中DBLINK有3种权限：CREATE DATABASE LINK（所创建的DBLINK只能是创建者自己使用，别的用户不能使用）、CREATE PUBLIC DATABASE LINK（PUBLIC表示所创建的DBLINK所有用户都可以使用）与DROP PUBLIC DATABASE LINK（删除公共的DBLINK）。可以使用如下授权语句给用户SCOTT授予CREATE PUBLIC DATABASE LINK和DROP PUBLIC DATABASE LINK两个权限：

GRANT CREATE PUBLIC DATABASE LINK,DROP PUBLIC DATABASE LINK TO SCOTT;

查询数据库的DBLINK可以使用SQL语句：SELECT * FROM DBA_DB_LINKS。

创建DBLINK一般有两种方式，第一种方式是在本地数据库tnsnames.ora文件中配置了要远程访问的数据库的TNS，例如这里配置了TNS_BJLHR，然后创建公共DBLINK：

CREATE PUBLIC DATABASE LINK

DBL_BJLHR CONNECT TO SCOTT IDENTIFIED BY TIGER USING 'TNS_BJLHR';

其中，DBL_BJLHR是创建的DBLINK名字，SCOTT/TIGER是登录到远程数据库的用户名/密码，然后在本地数据库中通过DBLINK访问远程数据库“TNS_BJLHR”中SCOTT.TB_TEST表，SQL语句如下所示：

SELECT * FROM SCOTT.TB_TEST@DBL_BJLHR;

创建DBLINK的第二种方式，是在本地数据库tnsnames.ora文件中没有配置要访问的远程数据库的时候，而直接将相关的内容写到DBLINK的配置中，如下所示：

CREATE DATABASE LINK DBL_BJLHR

CONNECT TO SCOTT IDENTIFIED BY TIGER

USING '(DESCRIPTION =

(ADDRESS_LIST =

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

)

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = BJLHR)

)

)';

其中，HOST为数据库的IP地址，SERVICE_NAME为数据库的SID，其实，两种方法配置DBLINK是差不多的，第二种方法不受本地服务的影响。

另外，当数据库参数GLOBAL_NAMES为TRUE时，要求数据库链接名称跟远端数据库名称一样。数据库全局名称可以用以下命令查出：

SELECT * FROM GLOBAL_NAME;

用以下语句来修改参数值：

ALTER SYSTEM SET GLOBAL_NAMES=FALSE/TRUE;

当数据库参数GLOBAL_NAMES为FALSE时，就不要求数据库链接名称跟远端数据库名称一样。

需要注意的是，对于Private类型的dblink，只有其创建者才能删除dblink。如果是Public的dblink，那么只要有“DROP PUBLIC DATABASE LINK”的权限就可以删除dblink：

SYS@PROD1> create database link sh.prod2_2 connect to sh identified by sh using 'PROD2';

Database link created.

SYS@PROD1> COL DB_LINK FORMAT A10

SYS@PROD1> COL HOST FORMAT A10

SYS@PROD1> COL OWNER FORMAT A10

SYS@PROD1> COL USERNAME FORMAT A10

SYS@PROD1> select * from dba_db_links d WHERE D.db_link LIKE '%PROD2_2%';

OWNER DB_LINK USERNAME HOST CREATED

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

SYS SH.PROD2_2 SH PROD2 15-DEC-17

SYS@PROD1> conn sh/sh

Connected.

SH@PROD1> create database link prod2 connect to sh identified by sh using 'PROD2';

Database link created.

SH@PROD1> conn / as sysdba

Connected.

SYS@PROD1> drop database link sh.PROD2;

drop database link sh.PROD2

ERROR at line 1:

ORA-02024: database link not found

11.4.2 一道OCM考题
1. Create DBLINK
1.1 Create DBLINK, should query on the PROD2 database all the table under the system user in the PROD1 Database.

1. Create Synonym
1.1 Create a sh.sales@PROD1 on the public synonyms in the PROD2, requires that all users can access.

题目要求在PROD2数据库上可以查询PROD1数据库上system用户下的所有表，并且在prod2上创建一个sh.sales@PROD1的同义词：
create public database link PROD1 connect to system identified by oracle using 'PROD1';
create public synonym sy_ss for sh.sales@PROD1;

1、概念：database link是定义一个数据库到另一个数据库的路径的对象，database link

允许你查询远程表及执行远程程序。

2、建立database link之前需要确认的事项：

确认从local database到remote database的网络连接是正常的，tnsping要能成功。

确认在remote database上面有相应的访问权限。

3、database link分类：

类型	Owner	描述
Private	创建database link的user拥有该database link	在本地数据库的特定的schema下建立的database link。只有建立该database link的schema的session能使用这个database link来访问远程的数据库。同时也只有Owner能删除它自己的private database link。
Public	Owner是PUBLIC.	Public的database link是数据库级的，本地数据库中所有的拥有数据库访问权限的用户或pl/sql程序都能使用此database link来访问相应的远程数据库。
Global	Owner是PUBLIC.	Global的database link是网络级的，When an Oracle network uses a directory server, the directory server automatically create and manages global database links (as net service names) for every Oracle Database in the network. Users and PL/SQL subprograms in any database can use a global link to access objects in the corresponding remote database. Note: In earlier releases of Oracle Database, a global database link referred to a database link that was registered with an Oracle Names server. The use of an Oracle Names server has been deprecated. In this document, global database links refer to the use of net service names from the directory server.

4、创建dblink所需的权限

Privilege	Database	Required For
CREATE DATABASE LINK	Local	Creation of a private database link.
CREATE PUBLIC DATABASE LINK	Local	Creation of a public database link.
CREATE SESSION	Remote	Creation of any type of database link.

1、如果需要创建全局 DBLink，则需要先确定用户有创建 dblink 的权限：

 
    select * from user_sys_privs where privilege like upper('%DATABASE LINK%');

如果没有，则需要使用 sysdba 角色给用户赋权：

 
     
 grant create public database link to dbusername;   

2、使用该用户登录 PL/SQL，使用命令：

 
 -- 第一种方法：要求数据库服务器 A 上 tnsnames.ora 中有 数据库 B 的映射   
 -- -- create database link 数据库链接名 connect to 用户名 identified by 密码 using '本地配置的数据的实例名';

采用图形配置界面则如下所示：

 
     
 -- 第二种方法：直接配置   
 -- 如果创建全局 dblink，必须使用 systm 或 sys 用户，在 database 前加 public。   
 create /* public */ database link dblink1   
 　　connect to dbusername identified by dbpassword   
 　　using '(DESCRIPTION =(ADDRESS_LIST =(ADDRESS =(PROTOCOL = TCP)(HOST = 192.168.0.1)(PORT = 1521)))(CONNECT_DATA =(SERVICE_NAME = orcl)))';   
    
 -- 数据库参数 global_name=true 时要求数据库链接名称跟远端数据库名称一样。数据库全局名称可以用以下命令查出   
 -- select * from global_name;   

3、查询数据：

 
 -- 查询、删除和插入数据和操作本地的数据库是一样的，只不过表名需要写成“表名@dblink服务器”而已。   
 select xxx FROM 表名@数据库链接名;

4、删除 DBLink

 
     
 drop /* public */ database link dblink1;   

5、创建和删除同义词

 
     
 create or replace synonym 同义词名 for 表名;   
 create or replace synonym 同义词名 for 用户.表名;   
 create or replace synonym 同义词名 for 表名@数据库链接名;   
 drop synonym 同义词名;   

6、创建和删除视图

 
 create or replace view 视图名 as (select 字段 from 用户.表名@dblink1);   
 drop view 视图名;

7、注意：

创建 DBLink 很简单，但是在使用中后台却出现锁，查看这个锁的方法可以去 console 中看到或者查询数据库。每次使用dblink查询的时候，均会与远程数据库创建一个连接，dblink 应该不会自动释放这个连接，如果是大量使用 dblink 查询，会造成 web 项目的连接数不够,导致系统无法正常运行，导致系统无正常运行。

Oracle DBLINK

作用:将多个不同地点的服务器的oracle数据库逻辑上看成一个数据库,也就是说在一个数据库中可以操作另一个远程的数据库中的对象。
语法:
CREATE [PUBLIC] DATABASE LINK dblink CONNECT TO user IDENTIFIED BY password USING ‘connect_string’;
DROP [PUBLIC] DATABASE LINK dblink;
注意:你必须有CREATE DATABASE LINK或CREATE PUBLIC DATABASE LINK的权限（可用sys身份去分配）,另外,在你要连接的数据库上的权限.
grant CREATE DATABASE LINK to hr;

参数说明:
    dblink: 你所创建的database link的名字,
    user和password:要连接的数据库的用户名和密码
    connect_string:可以是经过Net Manager配置的(tnsnames.ora)且经测试可以连接的服务名，不过也更直接用tnsnames里的字符串：(DESCRIPTION =

(ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = even.oracle.com)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME =orcl) )

通过SHOW PARAMETER GLOBAL_NAMES，可以查看到其值是FALSE或者TRUE。

一）对于GLOBAL_NAMES=FALSE的情况，则DBLINK的名称可以自定义，相关的过程如下：
实现从本地数据库连接到远端数据库服务器：
远程服务器要配置监听并且启动它
本地服务器要配置tnsnames

实现在本地服务器上连接到远程服务器数据库：

在本地数据库上，创建连接：
1、链接字符串即服务名，首先在本地配置一个服务名，地址指向远程的数据库地址,当然也直接写的连接字符串。
2、创建数据库链接（前提是已分配相应权限），
SQL> grant CREATE DATABASE LINK to hr;

Grant succeeded.

SQL> CREATE DATABASE LINK LinkRemoteTestDB CONNECT TO hr IDENTIFIED BY hr USING 'test';

Database link created.

当然也可以直接写连接字符串
SQL>create database link LinkRemoteTestDB2 connect to hr identified by hr
using 'TEST =
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = even.oracle.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = test)
    )
)';
则创建了一个以hr用户和TEST数据库的链接LinkRemoteTestDB.

3.使用database link来查询远程HR schema下的testdblink 表信息
SQL> select * from testdblink@LinkRemoteTestDB;

        ID NAME
---------- ----------------------------------------
         1 Watson
         2 John
通过执行select * from testdblink@LinkRemoteTestDB; 进行测试，结果OK

二）对于GLOBAL_NAMES = TRUE的情况，数据库链接（DATABASE LINK）的名字必须和数据库的名字相同：
在本地服务器上执行下面语句使GLOBAL_NAMES=TRUE：
SQL>ALTER SYSTEM SET GLOBAL_NAMES=TRUE;
再查询时，会有如下的错误：
SQL> select * from testdblink@LinkRemoteTestDB;
select * from testdblink@LinkRemoteTestDB
*
ERROR at line 1:
ORA-02085: database link LINKREMOTETESTDB.REGRESS.RDBMS.DEV.US.ORACLE.COM
connects to TEST.REGRESS.RDBMS.DEV.US.ORACLE.COM

登录远端数据库，通过执行
SQL>SELECT * FROM GLOBAL_NAME;得到其数据库全名为TEST.REGRESS.RDBMS.DEV.US.ORACLE.COM
用原方式SQL> CREATE DATABASE LINK LinkRemoteTestDB CONNECT TO hr IDENTIFIED BY hr USING 'test';创建过程不会出错，但执行“select * from

testdblink@LinkRemoteTestDB;”的时候，就会出现ORA-02085: database link LINKREMOTETESTDB.REGRESS.RDBMS.DEV.US.ORACLE.COM
connects to TEST.REGRESS.RDBMS.DEV.US.ORACLE.COM的错误了
所以需要采用下面的方式创建DBLINK:
SQL> create database link TEST.REGRESS.RDBMS.DEV.US.ORACLE.COM connect to HR identified by HR using 'TEST';

Database link created.再次执行
SQL> select * from testdblink@TEST.REGRESS.RDBMS.DEV.US.ORACLE.COM;

        ID NAME
---------- ----------------------------------------
         1 Watson
         2 John

SQL> UPDATE testdblink@TEST.REGRESS.RDBMS.DEV.US.ORACLE.COM t set t.name='WatsonModified' where id=1;

1 row updated.

SQL> select * from testdblink@TEST.REGRESS.RDBMS.DEV.US.ORACLE.COM;

        ID NAME
---------- ----------------------------------------
         1 WatsonModified
         2 John

执行成功了！！！！！

DBLINK其他相关的知识：
1、查看所有的数据库链接
SQL>select owner,object_name from dba_objects where object_type='DATABASE LINK';
SQL>select * from dba_db_links;

2、删除数据库连接
SQL> drop database link LinkRemoteTestDB;

Database link dropped.

database link的使用
基本语法
CREATE [SHARED][PUBLIC] database link link_name

      [CONNECT TO [user][current_user] IDENTIFIED BY password]
      [AUTHENTICATED BY user IDENTIFIED BY password]
      [USING 'connect_string']
说明：
1) 权限：创建数据库链接的帐号必须有CREATE DATABASE LINK或CREATE PUBLIC DATABASE LINK的系统权限，用来登录到远程数据库的帐号必须有CREATE SESSION权限。这两种权限都包含在CONNECT角色中（CREATE PUBLIC DATABASE LINK权限在DBA中）。一个公用数据库链接对于数据库中的所有用户都是可用的，而一个私有链接仅对创建它的用户可用。由一个用户给另外一个用户授权私有数据库链接是不可能的，一个数据库链接要么是公用的，要么是私有的。
2)link : 当source端的数据库GLOBAL_NAME=TRUE时，link名必须与远程数据库的全局数据库名global_name）相同；否则，可以任意命名。

3)current_user使用该选项是为了创建global类型的dblink。在分布式体系中存在多个数据库的话。如果想要在每一个数据库中都可以使用同样的名字来访问数据库a，那在每个数据库中都要创建一个到数据库a的db_link，太麻烦了。所以现在有这个选项。你只要创建一次。所有的数据库都可以使用这个db_link来访问了。要使用这个特性，必须有oracle nameserver或者ORACLE目录服务器。并且数据库a的参数global_names=true.具体我也没有创建过，没有这个环境。
4)connectstring：连接字符串，tnsnames.ora中定义远程数据库的连接串，也可以在创建dblink的时候直接指定。
5)username、password：远程数据库的用户名，口令。如果不指定，则使用当前的用户名和口令登录到远程数据库，当创建connected user类型的dblink时，需要如果采用数据字典验证，则需要两边数据库的用户名密码一致。
创建database link选项说明

取值说明

 不指定 

 默认值建立一个dedicated的连接，每一个使用database link的本地session都会对应有一个远程数据库的session。 

 SHARED 

 创建一个共享的数据库连接，同时要指定database link_authentication。
使用shared方式的 database link是数据库会限制到远程数据库的连接的数量，这样以避免过多的连接对远程数据库造成太大的压力。在使用shared database link的时候，到database link的连接会在连接以后与本地连接断开，为防止未授权的session使用此链接而要求在创建shared database link的时候必须要指定database link_authentication。
(from Oracle document: A shared database link is a link between a local server process and the remote database. The link is shared because multiple client processes can use the same link simultaneously.) 

共享链接更多资料
共享数据库链接是指该链接的多个用户可以共享同一个底层网络连接。例如，在有四位用户的MTS（多线程服务器）环境下，每一个共享服务器进程都将与远程服务器有一个物理链接，这四位用户共享这两个链接。
表面上，共享链接乍一听起来像是一件好事。在某些环境下的确如此，但是，当你考虑使用共享链接时，应当意识到这有许多局限性和警告：
如果你使用一个专用的服务器连接来连接到你的本地数据库，链接只能在你从那些连接中创建的多重会话间共享。在MTS环境里，每一个共享服务器进程潜在地打开一个链接。所有的会话被同一共享服务器进程提供并且分享被那个进程打开的任意共享链接。因为在MTS环境里的一个共享服务器进程能够服务于许多用户连接，共享链接的使用可能导致打开的链接远多于所必须的链接。用SHARED关键字建立共享数据库链接。还必须使用AUTHENTICATED BY 子句在远程系统上指定一有效的用户名和口令。如下命令建立一个共享的、公用的、连接用户数据库链接：
    CREATE SHARED PUBLIC database link GNIS
    AUTHENTICATED BY DUMMY_USER IDENTIFIED BY SECRET
    USING ‘GNIS’;

要获得创建链接和管理分布式系统的更多资料，请查阅Oracle Technology Network (http://otn.oracle.com/)。
使用AUTHENTICATED BY子句稍微有些困扰，但是由于实现共享链接的方式安全性决定它是必须的。这个例子中的用户名和口令DUMMY_USER/SECRET必须在远程系统上有效。然而，远程系统上使用的帐户仍就是连接用户的帐户。如果我以JEFF/SECRET登陆到我的本地数据库并使用我刚建好的共享链接，将会发生以下一系列事件：
为了打开链接，Oracle使用DUMMY_USER/SECRET向远程数据库授权。然后，Oracle试图使用HMAD/SECRET使我登陆到远程数据库。共享链接的主要目的是减少两个数据库服务器之间的底层网络连接数量。它们最适合于MTS环境，在那你拥有大量的通过这一链接访问远程数据库的用户。观念上，你想让用户数量超过共享服务器进程的数量。那么你可以通过为每一共享服务器进程打开一个链接而不是每位用户打开一个链接的方法，节省资源。

database link使用方式

取值说明

不指定	默认值建立一个private的database link
PUBLIC	公共连接，这样的连接可以被数据的所有的用户访问

database link用户验证方法 取值说明

不指定	默认值采取Connected User的验证方法
CONNECT TO CURRENT_USER	采取CURRENT_USER的验证方式
CONNECT TO user_name IDENTIFIED BY password	采取Fiexed User的验证方式

database link创建举例

SQL Statement Connects To Database Connects As Link Type

CREATE database link sales.us.americas.acme_auto.com USING ‘sales_us’;	sales using net service name sales_us	Connected user	Private connected user
CREATE database link foo CONNECT TO CURRENT_USER USING ‘am_sls’;	sales using service name am_sls	Current global user	Private current user
CREATE database link sales.us.americas.acme_auto.com CONNECT TO scott IDENTIFIED BY tiger USING ‘sales_us’;	sales using net service name sales_us	scott using password tiger	Private fixed user
CREATE PUBLIC database link sales CONNECT TO scott IDENTIFIED BY tiger USING ‘rev’;	sales using net service name rev	scott using password tiger	Public fixed user
CREATE SHARED PUBLIC database link sales.us.americas.acme_auto.com CONNECT TO scott IDENTIFIED BY tiger AUTHENTICATED BY anupam IDENTIFIED BY bhide USING ‘sales’;	sales using net service name sales	scott using password tiger, authenticated as anupam using password bhide	Shared public fixed user

不使用TNS Name一例：
CREATE database link link_name
CONNECT TO user IDENTIFIED BY screct
USING '(DESCRIPTION =
    (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = sales.company.com)(PORT = 1521))
    )
    (CONNECT_DATA =
        (SERVICE_NAME = sales)
    )
)';
database link的使用
-- 最简单的用法
SELECT * FROM table_name@database link;
-- 不想让使用的人知道database link的名字的时候
-- 建一个别名包装一下
CREATE SYNONYM table_name FOR table_name@database link;
SELECT * FROM table_name;
-- 或者，也可以建立一个视图来封装
CREATE VIEW table_name AS SELECT * FROM table_name@database link;
database link删除
-- 删除public类型的database link
DROP PUBLIC database link link_name;
-- 删除非public类型的database link
-- 注意：只有owner自己能删除自己的非public类型database link
DROP database link link_name;
查看database link的信息
查看系统database link的基本信息
DBA_DB_LINKS (ALL_DB_LINKS/USER_DB_LINKS)
DBA_DB_LINKS视图为每一定义的链接返回一行。OWNER 列和DB_LINK列分别显示了这一链接的所有者及名称。对公用数据库链接，OWNER列将包含’PUBLIC’。如果你建立固定用户链接，用户名应在DBA_DB_LINKS视图的USERNAME列里。ALL_DB_LINKS 视图和 USER_DB_LINKS视图与 DBA_DB_LINKS视图相类似-它们分别显示了你能够访问的所有链接及你所拥有的全部链接。
COL OWNER FOR A15
COL DB_LINK FOR A25
COL HOST FOR A25
COL USERNAME FOR A15
SELECT * FROM DBA_DB_LINKS;
DBA_OBJECTS (ALL_OBJECTS/USER_OBJECTS)
在这个视图里面只能查询到系统有那些database link以及他们的owner，创建时间等信息。
COL OWNER FOR A15
COL OBJECT_NAME FOR A25
COL OBJECT_TYPE FOR A25
SELECT OWNER,OBJECT_NAME,OBJECT_TYPE FROM DBA_OBJECTS WHERE OBJECT_TYPE LIKE 'database link';
查看保存的Fixed user database link所保存的用户密码,该密码是经过加密的。
这是比较危险的一件事，有必要对表sys.link$的权限进行限制。
col host for a15
col userid for a15
col passwordx for a40
col name for a20
select name, host, userid, passwordx from sys.link$;
通过database link去SELECT远程数据库的一个表也是一个事务吗？
select * from v$transaction;
-- 没有记录，说明没有事务
-- 通过database link连接远程数据库，select 其中一个表
select * from bbs_news@mylink
select * from v$transaction;
-- 发现有一条记录。
解释
    因为本地数据库只是将对应的sql发送给远程数据库执行，接受remote db返回的结果，但他并不知道是否该sql修改了数据；所以需要为select 语句也标示一个事务。
    具体可以参考otn 分布式数据库手册，所以在用database link远程访问时,要加上set transaction read only;
close database link
ALTER SESSION CLOSE database link sales;

其实，dblink的相应属性对应了Oracle的数据字典link$,任何针对dblink的操作都是操作该数据字典。在9i的时候，如果Oracle的global_name仅包括db_name，也就是说DB_DOMAIN的值为空。那么这个时候建立的数据库链，在数据库修改全局名GLOBAL_NAME之后（修改为db_name.db_domain格式），会无法删除。
如果要产生数据库链，必须将GLOBAL_NAME改回DB_NAME格式，即去掉后面的DOMAIN，但是这个时候，RENAME操作会自动添加域名，使得Oracle全局名无法恢复到初始状态。因此在这情况下，如果需要删除dblink，只能直接操作link$数据字典

delete from link$ where owner#=user_id and name=dblink_name

当然直接操作数据字典是危险的，最好做好备份，然后再进行操作。

利用dblink执行ddl

我们知道任何ddl语句都无法在dblink中直接执行，示例如下

SQL> desc db_test;
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER(38)

SQL> drop table db_test@DBLINK_CONNECTED_HR;
drop table db_test@DBLINK_CONNECTED_HR
*
ERROR at line 1:
ORA-02021: DDL operations are not allowed on a remote database

通过创建存储过程，使得能在dblink中执行ddl语句。注意需在目标数据库的相应用户下创建存储过程，具体如下

SQL> exec dbms_utility.EXEC_DDL_STATEMENT@dblink('drop table db_test')
PL/SQL procedure successfully completed.

SQL> desc db_test;
ERROR:
ORA-04043: object db_test does not exist

dblink的限制
You cannot perform the following operations using database links:
Grant privileges on remote objects
Execute DESCRIBE operations on some remote objects. The following remote objects, however, do support DESCRIBE operations:
        Tables
        Views
        Procedures
        Functions
Analyze remote objects
Define or enforce referential integrity
Grant roles to users in a remote database
Obtain nondefault roles on a remote database. For example, if jane connects to the local database and executes a stored procedure that uses a fixed user link connecting as scott, jane receives scott's default roles on the remote database. Jane cannot issue SET ROLE to obtain a nondefault role.
Execute hash query joins that use shared server connections
Use a current user link without authentication through SSL, password, or NT native authentication

ORA-02024: database link not found

删除database link 时发现报错： ORA-02024: database link not found

1. 确认database link是否public database link ,如果是，不必是这个dblink的owner, 有
足够的权限即可删除。

2. 如果是private database link，那么需要owner来删除，否则会报错。

3. 创建前后更改了global_name ,原来global_name=true, 创建dblink后被更改为了false .
这种情况下即使再更改回来也不能正常删除dblink。详见：
Cannot drop a database link after changing the global_name ORA-02024 (文档 ID 382994.1)

删除 Dblink 报错 ORA-02024: database link not found 的解决方法

朋友说删除DBLINK 报错： ORA-02024: database link not found。

在Metalink上搜到了2篇与这个错误有关的文章：

Cannot drop a database link after changing the global_name ORA-02024 [ID 382994.1]

http://blog.csdn.net/tianlesoftware/archive/2011/01/23/6160082.aspx

ORA-02024: Database Link Not Found [ID 1058949.1]

http://blog.csdn.net/tianlesoftware/archive/2011/01/23/6160080.aspx

两篇文章分别讲到了2种情况。

一 . DBLINK所有者不一致造成

我们来演示一下这种情况。

--创建dblink

SQL> conn system/admin;

已连接。

SQL> create database link dave connect to system identified by admin using '

2 (DESCRIPTION =

3 (ADDRESS_LIST =

4 (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.6.100)(PORT = 1521))

5 )

6 (CONNECT_DATA =

7 (SERVICE_NAME = newccs)

8 )

9 )

10 ';

数据库链接已创建。

DBLINK 创建这块可以参考官网说明：

CREATE DATABASE LINK

http://download.oracle.com/docs/cd/E11882_01/server.112/e17118/statements_5005.htm#SQLRF01205

--查看验证dblink

SQL> select name from v$database@dave;

NAME

---------

NEWCCS

SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

会话已更改。

SQL> select * from all_db_links;

OWNER DB_LINK USERNAME HOST CREATED

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

SYSTEM DAVE SYSTEM 2011-01-23 21:17:31

--我们用错误的所有者来删除看看

SQL> drop public database link dave;

drop public database link dave

第 1 行出现错误:

ORA-02024: 未找到数据库链接

--用正确的用户来删除

SQL> drop database link dave;

数据库链接已删除。

二. Global_name 造成

先看官网的一段说明：

If the value of the GLOBAL_NAMES initialization parameter is TRUE, then the database link must have the same name as the database to which it connects. If the value of GLOBAL_NAMES is FALSE, and if you have changed the global name of the database, then you can specify the global name.

The maximum number of database links that can be open in one session or one instance of an Oracle RAC configuration depends on the value of the OPEN_LINKS and OPEN_LINKS_PER_INSTANCE initialization parameters.

Global_names 是一个布尔值，global_names的作用是创建db link时是否强制使用远程数据库的global_name,如果global_names=true,则db link name必须要求是remote database的global_name,否则创建之后db link 不能连同，缺省值是false。当global_name 为False时，如果我们修改了global_name，那么在dblink中也要相应的指定global_name. 并且当我们修改了global_name后，之前存在的我们dblink也无法删除。

Global_name 是由db_name.db_domain构成。

有关这几个参数的说明，参考：

DBID,SID,DB_NAME,DB_DOMAIN,INSTANCE_NAME,DB_UNIQUE_NAME,SERVICE_NAMES 及监听参数的说明

http://blog.csdn.net/tianlesoftware/archive/2010/12/20/6086066.aspx

CREATE DATABASE LINK

http://download.oracle.com/docs/cd/E11882_01/server.112/e17118/statements_5005.htm#SQLRF01205

open_links_per_instance 和 open_links 参数说明

http://www.cndba.cn/cndba/dave/article/1431

下面来看示例：

SQL> show parameter global_names

NAME TYPE VALUE

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

global_names boolean FALSE

SQL> select * from global_name;

GLOBAL_NAME

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

NEWCCS

SQL> create database link dave connect to system identified by admin using 'newccs';

数据库链接已创建。

SQL> select * from dba_db_links;

OWNER DB_LINK USERNAME HOST CREATED

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

SYSTEM DAVE SYSTEM newccs 2011-01-23 21:45:41

SQL> select name from v$database@dave;

NAME

---------

NEWCCS

这里要注意一点，我这里创建的dblink 使用的是监听名：newccs. dba_db_links表中的host显示的是这个监听的名字。在第一个示例中，我创建dblink 是使用命令来写的，那种情况下host 为空。

在上面提到，当global_names为False的情况下，如果我们修改了global_name，之前创建的dblink 在删除的时候也会包ORA-02024的错误。甚至我们把global_name 改变成原来的值，也无法删除。因为这种改变没有生效。除非我们更新props$ 表。

先来验证global_name 修改后生效问题。

SQL> select * from global_name;

GLOBAL_NAME

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

NEWCCS

SQL> alter database rename global_name to newccs.tianlesoftware.com;

数据库已更改。

SQL> select * from global_name;

GLOBAL_NAME

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

NEWCCS.TIANLESOFTWARE.COM

SQL> alter database rename global_name to newccs;

数据库已更改。

SQL> select * from global_name;

GLOBAL_NAME

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

NEWCCS.TIANLESOFTWARE.COM

--这里并没有生效。

SQL> drop database link dave;

drop database link dave

第 1 行出现错误:

ORA-02024: 未找到数据库链接

从上面的操作，验证了如果仅通过alter 命令，是无法让global_name 还原成原来的值的。并且当global_name 发生改变后，已经存在的dblink也无法删除。

解决这个问题的方法，就是用sys用户来修改props$表：

SQL> update props$ set value$ = '' where name ='GLOBAL_DB_NAME';

示例：

SQL> conn / as sysdba;

已连接。

SQL> update props$ set value$ = 'NEWCCS' where name ='GLOBAL_DB_NAME';

已更新 1 行。

SQL> commit;

提交完成。

然后切换到dblink 的用户，删除dblink：

SQL> conn system/admin;

已连接。

SQL> drop database link dave;

数据库链接已删除。

如果还报这种错误，可以刷新三次shared pool，在执行drop命令：

alter system flush SHARED_POOL;

如果刷新shared pool后还无法drop，那么就需要重启DB，在删除了。

Cannot drop a database link after changing the global_name ORA-02024 (Doc ID 382994.1)

In this Document

    Symptoms 

    Changes 

    Cause 

    Solution 

    References 

APPLIES TO:

 
    Oracle Database - Enterprise Edition - Version 9.2.0.6 and later 
  

 
    Information in this document applies to any platform. 
  

 
    ***Checked for relevance on 14-Nov-2014*** 
  

Not able to drop a database link after changing the global_name of the database

 
   Earlier global_name had did not have domain name attached to it. 
  

The newly added global_name has a domain name attached to it.

 
   When trying to drop the database link after this change throws the following error  
  

 
   ORA-02024: database link not found  
  

 
   But database link is present and the query on user_db_links displays the value  
  

 
   Example:-  
  

 
   SQL> select * from global_name;  
  

 
   GLOBAL_NAME  
  

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

 
   DB10GR2  
  

 
   SQL> create database link l1 connect to scott identified by tiger;  
  

 
   Database link created.  
  

 
   SQL> select db_link from user_db_links;  
  

 
   DB_LINK  
  

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

L1

 
   SQL> alter database rename global_name to DB10GR2.WORLD;  
  

 
   Database altered.  
  

 
   SQL> select * from global_name;  
  

 
   GLOBAL_NAME  
  

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

 
   DB10GR2.WORLD  
  

 
   SQL> drop database link l1;  
  

 
   drop database link l1 
  

ERROR at line 1:

ORA-02024: database link not found

 
   Even if the global_name is changed back to the original value, the same errors  
  

 
   occurs. 
  

Initially when a database is created without domain in the global name, null will used from domain as opposed to .world in earlier releases

 
   Later on when the global_name is altered to contain the domain part also, this domain remains even when the global_name is altered back a name without domain name  
  

 
   Example :-  
  

 
     SQL> select * from global_name;  
   
    GLOBAL_NAME  
   
    ---------------------------------------------------------  
   
    DB10GR2  
   
    SQL> alter database rename global_name to DB10GR2.WORLD;  
   
    Database altered.  
   
    SQL> select * from global_name;  
   
    GLOBAL_NAME  
   
    ---------------------------------------------------------  
   
    DB10GR2.WORLD  
   
    SQL> alter database rename global_name to DB10GR2;  
   
    Database altered.  
   
    SQL> select * from global_name;  
   
    GLOBAL_NAME  
   
    ---------------------------------------------------------  
   
    DB10GR2.WORLD

 
   The only option left to correct this is to update the base table props$ 
  

There are two solutions:

1. Update sys.link$ and rename the name column to make it match so that the command to drop public database link works.

 
       1.1. Take a complete consistent backup of the database  
  

 
       1.2. Execute the following:  
  

 
     $sqlplus /nolog  
   
     connect / as sysdba  
   
    SQL> update props$ set value$ = '' where name ='GLOBAL_DB_NAME';  
   
    SQL>commit;

 
       1.3 Drop the database link: 
  

 
           1.3.1. Connect as the schema user that owns the DBLINK and try to drop it. 
  

 
   If you still get ORA-2024: database link not found , that means the domain name is in your cache and needs to be cleared. 
  

 
           1.3.2. Flush shared pool thrice and retry drop database link. 
  

 
    alter system flush SHARED_POOL; 
   
    alter system flush SHARED_POOL; 
   
    alter system flush SHARED_POOL;

1.3.3. If step b doesn't help, you need to bounce your database and try to drop the database link.

1.3.4. Once the database link is dropped, the global_name can be changed back to the desired name containing domain part using the alter database rename global_name statement

2. The second solution consists on deleting the database link directly from sys.link$:

 
            2.1. Take a complete consistent backup of the database or use CTAS can be used to backup sys.link$: 
  

 
     $sqlplus /nolog 
   
     connect / as sysdba 
   
    SQL> create table backup_link$ as select * from sys.link$:

2.2. Delete the DBLINK as follows:

 
     $sqlplus /nolog 
   
     connect / as sysdba 
   
    SQL> delete sys.link$ where name='db_link_name>'; 
   
    SQL>commit;

 
            2.3. Verify if the operaion s correctly proceeded: 
  

 
    select db_link, username, host from user_db_links; 
  

Reference

Bug 3675157 - ORA-02024: CANNOT DROP DBLINK AFTER THE GLOBAL_NAME PARAMETER IS CHANGED.

 
   Still have questions ? 
  

To discuss this information further with Oracle experts and industry peers, we encourage you to review, join or start a discussion in the My Oracle Support Streams and Distributed Database Community

About Me

.............................................................................................................................................

● 本文作者：小麦苗，部分内容整理自网络，若有侵权请联系小麦苗删除

● 本文在itpub（http://blog.itpub.net/26736162/abstract/1/）、博客园（http://www.cnblogs.com/lhrbest）和个人微信公众号（xiaomaimiaolhr）上有同步更新

● 本文itpub地址：http://blog.itpub.net/26736162/abstract/1/

● 本文博客园地址：http://www.cnblogs.com/lhrbest

● 本文pdf版、个人简介及小麦苗云盘地址：http://blog.itpub.net/26736162/viewspace-1624453/

● 数据库笔试面试题库及解答：http://blog.itpub.net/26736162/viewspace-2134706/

● DBA宝典今日头条号地址：http://www.toutiao.com/c/user/6401772890/#mid=1564638659405826

.............................................................................................................................................

● QQ群号：230161599（满）、618766405

● 微信群：可加我微信，我拉大家进群，非诚勿扰

● 联系我请加QQ好友（646634621），注明添加缘由

● 于 2018-01-01 06:00 ~ 2018-01-31 24:00 在魔都完成

● 文章内容来源于小麦苗的学习笔记，部分整理自网络，若有侵权或不当之处还请谅解

● 版权所有，欢迎分享本文，转载请保留出处

.............................................................................................................................................

● 小麦苗的微店：https://weidian.com/s/793741433?wfr=c&ifr=shopdetail

● 小麦苗出版的数据库类丛书：http://blog.itpub.net/26736162/viewspace-2142121/

● 好消息：小麦苗OCP、OCM开班啦，详情请点击：http://blog.itpub.net/26736162/viewspace-2148098/

.............................................................................................................................................

使用微信客户端扫描下面的二维码来关注小麦苗的微信公众号（xiaomaimiaolhr）及QQ群（DBA宝典），学习最实用的数据库技术。

小麦苗的微信公众号小麦苗的DBA宝典QQ群2 《DBA笔试面宝典》读者群小麦苗的微店

.............................................................................................................................................