Shulou(Shulou.com)06/01 Report--

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 >

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.