Instructions for using oracle database link

Function: treat multiple oracle databases logically as one database, that is, you can manipulate objects in another database in one database.

Simple grammar:

CREATE [PUBLIC] DATABASE LINK dblink CONNECT TO user IDENTIFIED BY password USING 'connect_string'; DROP [PUBLIC] DATABASE LINK dblink

Note: you must have CREATE DATABASE LINK or CREATE PUBLIC DATABASE LINK permissions. In addition, you must have CREATE SESSION permissions on the database you want to connect to.

Grammatical explanation:

Dblink: the connection name to be used later in the sql statement. In the init.ora file, if GLOBAL_NAMES=true, the dblink must be the same as the database global name (SELECT * FROM GLOBAL_NAME;). For convenience, you can ALTER SYSTEM SET GLOBAL_NAMES=FALSE

User and password: legal user name and password of the database to connect to

Connect_string: can be Net Configuration Assistant configured (tnsnames.ora) and tested to connect to the alias, such as: orcl123, but prone to problems, always error: unable to parse the string. It is better to write in this form (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.0.78) (PORT = 1521)) (CONNECT_DATA = (SERVICE_NAME = orcl)))

Use:

SELECT * FROM USER_TABLES@dblink

UPDATE jobs@dblink SET min_salary = 3000 WHERE job_id = 'SH_CLERK'

Links between databases are established on DATABASE LINK. To create a DB LINK, you must first set the link string on each database server.

1. The link string is the service name. First, configure a service name locally. The address points to the remote database address, and the service name is the name of the database chain you want to use in the future:

2. Create a database link, go to the system administrator SQL > operator, and run the command:

SQL > CREATE PUBLIC DATABASE LINK DBL_mesdb15

CONNECT TO scott identified by tiger

Using'(DESCRIPTION =

(ADDRESS_LIST =

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

)

(CONNECT_DATA =

(service_name=mesdb)

)

)'

We create a link DBL_mesdb15 between the scott user and the MESDB database, and we query the scott data of MESDB:

SQL > select * from emp@DBL_mesdb15

3. Create synonyms. In order to make the distributed operation more transparent, there is a synonym object synonym in the ORACLE database.

SQL > create synonym bjscottemp for emp@DBL_mesdb15

So you can use bjscottemp instead of the distributed link operation emp@DBL_mesdb15 with the @ symbol.

4. Check all the database links, go to the system administrator SQL > operator, and run the command:

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

5. View the database connection

Sql > select owner, db_link from dba_db_links

Ower db_link

Public TEST.US.ORACLE.COM

6. Delete the database connection

First check the database connection from step 3 to get the name of its db_link

Sql > drop public database link DBL_mesdb15.US.ORACLE.COM

Database connection discarded