建立oracle数据库链接 Oracle数据库创建数据库连接（DBLink）详细讲解

2.1数据库创建数据库连接(DBLink)

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

第一步：查询用户是否具备创建DBLink的权限，并赋予权限

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

(2) grant create public database link,drop public database link to test_1;

第二步：创建DBLink

已经配置本地服务：

create public database link test_others_course

connect to test_others identified by "456"

using'ORCL';

直接建立连接：

create public database link test_others_course

connect to test_others identified by "456"

using'(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = ORCL)

)

)';

第三步：查询DLink，删除DBLink

查询DLink(在dba下执行)：select * from dba_db_links;

select owner,object_name from dba_objects where object_type='DATABASE LINK';

删除DBLink：drop public database link test_others_course;

第四步：执行DBLink

select * from course@test_others_course;

3.1赋予/移除用户增、删、改、查其他用户所属表权限

3.1.1所有表

(1)grant/revoke update any table to test_1;

(2)grant/revoke insert any table to test_1;

(3)grant/revoke delete any table to test_1;

(4)grant/revoke select any table to test_1;

3.1.2单独表

(1)grant/revoke select on test_2.teacher to test_1;

(2)grant/revoke delete on test_2.teacher to test_1;

(3)grant/revoke insert on test_2.teacher to test_1;

(4)grant/revoke update on test_2.teacher to test_1;

3.1.3查询用户拥有权限

select * from dba_sys_privs where grantee='TEST_1';(用户名要大写)

3.1.3查询用户拥有角色

select * from dba_role_privs where grantee='TEST_1';(用户名要大写)