Oracle-dblink查看、创建、删除与java实际例子

目录

一、含义

二、查看数据库创建的dblink

三、dblink权限

四、创建dblink连接

五、删除dblink连接

六、使用dblink连接

七、Java实际例子

一、含义
dblink(Database Link)数据库链接顾名思义就是数据库的链接 ，就像电话线一样，是一个通道，当我们要在本地数据库，跨库访问另外一个数据库表中的数据时，本地数据库中就必须要创建远程数据库的dblink,通过dblink本地数据库可以像访问本地数据库一样访问远程数据库表中的数据。

二、查看数据库创建的dblink
执行以下sql可得当前数据库创建的所有dblink连接

select * from dba_db_links;

OWNER解释
Owner 描述
Private 私有 在本地数据库的特定的schema下建立的databaselink。只有建立该databaselink的schema的用户能使用这个databaselink来访问远程的数据库。同时也只有Owner能删除它自己创建的私有dblink。
Public 公有 Public的databaselink是数据库级的，本地数据库中所有的拥有数据库访问权限的用户或pl/sql程序都能使用此databaselink来访问相应的远程数据库。
Global 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.

一句话：私有dblink连接只有创建者用户可使用，公有dblink连接当前数据库用户都可使用

三、dblink权限
创建数据库链接的帐号必须有以下系统权限

权限 解释
CREATE DATABASE LINK 创建私有dblink连接
CREATE PUBLIC DATABASE LINK 创建公有dblink连接
CREATE SESSION
远程登录数据库

查询用户（RUOYI）是否拥有创建dblink权限，执行以下sql

select * from user_sys_privs where privilege like upper(’%DATABASE LINK%’)
AND USERNAME=‘RUOYI’
若返回结果如图，则表示该用户拥有表示具备创建（公有和私有）database link权限

若是返回为空，则表示该用户没有创建dblink权限

在sys用户下，把CREATE PUBLIC DATABASE LINK，DROP PUBLIC DATABASELINK（删除dblink连接）权限授予给你的用户：

grant CREATE PUBLIC DATABASE LINK,DROP PUBLIC DATABASE LINK to RUOYI;

再执行上面查看是否具备权限的sql语句，会得到如图所示，则该用户拥有创建公有、私有、删除dnlink连接权限

四、创建dblink连接
以创建公有dblink连接为例

根据oracle-listener.ora文件得到

数据库的ssid=DMSF3201

HOST=6.1.144.99

create public database link dblink名称
connect to 用户名 identified by “密码”
　　using ‘(DESCRIPTION =
　　(ADDRESS_LIST =
　　(ADDRESS = (PROTOCOL = TCP)(HOST = 数据库的ip地址 )(PORT = 1521))
　　)
　　(CONNECT_DATA =
　　(SERVICE_NAME = 数据库的ssid)
　　)
　　)’;
五、删除dblink连接
　　DROP PUBLIC DATABASE LINK dblink名称;
六、使用dblink连接
SELECT …… FROM 表名@数据库dblink链接名;
七、Java实际例子

本地数据库：IP：6.1.144.98 账号：RUOYI 密码：RUOYI

远程数据库：IP：6.1.144.99 账号：RUOYI 密码：RUOYI

本地数据库未执行以下程序

查询dblink如图

执行以下程序

import com.alibaba.druid.pool.DruidDataSource;
import com.maxvision.duty.exception.DutyRuntimeException;
import org.apache.ibatis.jdbc.SqlRunner;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.Map;

public class Test {
public Connection getJdbcConnection(String url, String username, String pwd) throws SQLException {
DruidDataSource druidDataSource = new DruidDataSource();
druidDataSource.setUrl(url);
druidDataSource.setUsername(username);
druidDataSource.setPassword(pwd);
druidDataSource.setDriverClassName(“oracle.jdbc.driver.OracleDriver”);
druidDataSource.setMaxWait(30000);
druidDataSource.setMaxActive(2000);
druidDataSource.setMinIdle(5);
druidDataSource.setRemoveAbandoned(false);
druidDataSource.setRemoveAbandonedTimeout(4000);
druidDataSource.setLogAbandoned(true);
druidDataSource.setTestOnBorrow(true);
druidDataSource.setTestOnReturn(true);
druidDataSource.setTestWhileIdle(true);
druidDataSource.setMinEvictableIdleTimeMillis(180000);
druidDataSource.setInitialSize(10);
druidDataSource.setTimeBetweenEvictionRunsMillis(60000);
druidDataSource.setBreakAfterAcquireFailure(true);
druidDataSource.setConnectionErrorRetryAttempts(5);
druidDataSource.setTimeBetweenConnectErrorMillis(5);
druidDataSource.setValidationQuery(“SELECT 1 FROM SYS.DUAL”);
return druidDataSource.getConnection();
}

/**
 * 创建数据库dblink
 *
 * @param dbLinkName
 * @param account    dblink用户名
 * @param pwd        dblink密码
 * @param dsName
 * @param ip
 */
public void createDBLink(String dbLinkName, String url, String account, String pwd, String dsName, String ip) {
    try (Connection conn = getJdbcConnection(url, account, pwd)) {
        final SqlRunner runner = new SqlRunner(conn);
        StringBuilder stringBuilder = new StringBuilder();
        //查看数据库是否存在dblink
        Map<String, Object> maps = runner.selectOne(stringBuilder.append("select COUNT(*) count from dba_db_links WHERE DB_LINK = '").append(dbLinkName).append("'").toString());
        if (Integer.parseInt(maps.get("COUNT").toString()) > 0) {
            //数据库存在dblink
            return;
        }
        stringBuilder = new StringBuilder();
        stringBuilder.append("create public database link ");
        stringBuilder.append(dbLinkName);
        stringBuilder.append(" connect to ");
        stringBuilder.append(account).append(" identified by ");
        stringBuilder.append("\"").append(pwd).append("\"").append(" using ");
        stringBuilder.append("'(DESCRIPTION =(ADDRESS_LIST =(ADDRESS =(PROTOCOL = TCP)(HOST = ").append(ip);
        stringBuilder.append(")(PORT = 1521)))(CONNECT_DATA =(SERVICE_NAME = ").append(dsName).append(")))'");
        runner.run(stringBuilder.toString());
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

public static void main(String[] args) {
    //本地数据库和远程数据库测试方便， 账号密码都是一样 RUOYI
    Test test = new Test();
    //本地数据库
    String sourceUrl = "jdbc:oracle:thin:@6.1.144.98:1521:DMSF3201";
    //远程数据库
    String targetUrl = "jdbc:oracle:thin:@6.1.144.99:1521:DMSF3201";
    //远程数据库获取sid(DMSF3201)
    String dsName = targetUrl.split("@")[1].split(":")[2];
    String account = "RUOYI";
    String pwd = "RUOYI";
    String dbLinkName = "TestDblink";
    //远程数据库IP
    String ip = "6.1.144.99";
    //创建数据库dblink
    test.createDBLink(dbLinkName, sourceUrl, account, pwd, dsName, ip);
}

}
运行后，查询数据库

接着在本地数据库（98）中执行查询远程数据库（99）表记录

SELECT …… FROM 表名@数据库dblink链接名;