Create Oracle Database link

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=ScottPassWord=ScottTNS_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 [e-mail];create or replace synonym scott.real_table FOR scott.synonym_table;