The use of dblink
1. Create a global link (use one local user to access other users' tables)
Syntax:
Create public database link link name CONNECT TO local username IDENTIFIED BY password USING 'local database instance name'
SQL > create user upch identified by System13579
User created.
SQL > grant dba,resource,connect to upch
Grant succeeded.
SQL > conn upch/System13579
Connected.
SQL > create table t (a number)
Table created.
SQL > insert into t values (1)
1 row created.
SQL > insert into t values (2)
1 row created.
SQL > select * from t
A
-
one
two
SQL > conn / as sysdba
Connected.
SQL > create public database link tong connect to upch identified by System13579 using 'orcl'
SQL > select * from tweets;-- access the t table under upch users
A
-
one
two
SQL >
two。 Create link remotely
Syntax:
Create / * public * / database link tong connect to remote username identified by password using'(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = remote IP address) (PORT = 1521)) (CONNECT_DATA = (SERVICE_NAME = remote SID value)'
[oracle@localhost dbs] $sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Wed Jun 28 14:47:53 2017
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Database link created.
SQL > select * from t@tong1
A
-
one
two
SQL > col owner for A10
SQL > col db_link for A10
SQL > col host for A150
SQL > select * from dba_db_links;-- check how many dblink there are in the database
OWNER DB_LINK USERNAME HOST CREATED
SYS TONG1 UPCH (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = 172.16.8.161) (PORT = 1521)) (CONNECT_DATA = (SERVICE_NAME = orcl) 28-JUN-17
SQL > drop database link tong1;-- Delete dblink
Database link dropped.
SQL >
