Create Oracle Database link

最新推荐文章于 2024-12-22 22:12:59 发布

原创最新推荐文章于 2024-12-22 22:12:59 发布 · 1.3k 阅读

0 ·

CC 4.0 BY-SA版权

Oracle 专栏收录该内容

17 篇文章

订阅专栏

本文详细介绍了如何在Oracle数据库中创建和使用数据库链接，包括配置tnsnames.ora文件、创建数据库链接、测试链接有效性、查看所有数据库链接、删除数据库链接、为远程客户端创建新用户并授予相应权限。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1. New TNS on tnsnames.ora:

e.g.

TESTDBLINK =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 100.40.221.34)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = SIDGZ)
    )
  )

if you cannot access the file, write above strings into <TNS_NAME> directly.

2. New database link on oracle:

CREATE DATABASE LINK "BASE_DATA_LINK"
   CONNECT TO "<UserName>" IDENTIFIED BY VALUES '<PassWord>' USING '<TNS_NAME>';
UserName=Scott
PassWord=Scott
TNS_NAME=TESTDBLINK

3. Test if ok:

 select * from dual@SIDGZ

4. Check all DB links:

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

5. Delete DB link:

drop database link <LinkName>;

6. Create new user for remote client:

create user <userName> identified by <password>;

7. Grant user some necessary access:

grant connect, resources, umlimited tablespace to <newUserName>;

8. Grant user the specified table:

grant select, update on <tableName>@<LinkName> to <newUserName>;

8.1 if you don't want to specify <mainUser> , you can consider to use below 'synonym':

create synonym <synonymTableName> for <tableName>@<LinkName>;

then you directly use when logon as <newUserName>: select * from <tableName>; (instead of : select * from <tableName>@<LinkName>)

8.2 You can just grant some columns:

grant update (col1, col2) on <tableName> to <newUserName>;

Other info:

update view_1 set command = command;

在某些情况下可以更新视图；

官方链接：

http://docs.oracle.com/cd/B12037_01/server.101/b10759/statements_5005.htm

The user who issues this statement must be a global user registered with the LDAP directory service.

You can create a synonym to hide the fact that a particular table is on the remote database. The following statement causes all future references to emp_table to access the employees table owned by hr on theremote database:

CREATE SYNONYM emp_table 
   FOR oe.employees@remote.us.oracle.com;

create or replace synonym scott.real_table FOR scott.synonym_table;