Oracle Database Link Usage Instructions

Role: Logically treat multiple Oracle databases as a single database, meaning that objects in another database can be manipulated in one database

Simple syntax:

CREATE [public] The DATABASE LINK dblink CONNECT to the user identified by password USING ' connect_string '; DROP [public] DATABASE LINK Dblink;

Note: You must have the permissions to create database link or create public link, and on the database you want to connect to, you must have the Create session permission .

Syntax Explanation:
Dblink: The connection name to use 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, Can alter SYSTEM SET Global_names=false;
User and Password: the legitimate username and password of the database to be connected
Connect_string: Can be a net Configuration Assistant configured (Tnsnames.ora) and tested to connect aliases, such as: orcl123, but prone to problems, the old prompt error: The string could not be parsed. This is best written in this form (DESCRIPTION = (Address_list = (ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.0.78) (PORT = 1521)) (connect_ DATA = (service_name = ORCL)))

using:  
SELECT * from  [email protected] ;  
Update [email  Protected]  set min_salary = WHERE job_id = ' Sh_clerk ';

links between databases are based 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, the service name for the future you want to use the database chain name:    
2, create a database link, enter system administrator SQL > operator, Run command:  
sql>create public DATABASE LINK dbl_mesdb15 
CONNECT to Scott identified by tiger& nbsp
Using ' (DESCRIPTION =
(address_list =
(ADDRESS = (PROTOCOL = TCP) (HOST = 10.190.113.15) (PORT = 1521))
)
( Connect_data =
(service_name=mesdb)
)
) ';

Create a link to the Scott user and Mesdb database dbl_mesdb15, we query mesdb Scott data:  
Sql>select * From [email protected] _mesdb15; 
3, establishing synonyms, in order to make the distributed operation more transparent, Synonym  object with synonyms in Oracle database;
sql>create synonym bjscottemp for [email protected] _mesdb15; 
You can instead use BJSCOTTEMP to replace distributed link operations with the @ symbol [email protected] _mesdb15.  
4, view all the database links, enter the system administrator sql> operator, run the command:  
Sql>select owner,object_name from Dba_objects where Object_type= ' DATABASE LINK ';
5, view database connection
Sql> Select owner, db_link from dba_db_links; 
ower db_link
Public TEST. US. oracle.com

6. Delete the database connection
See the database connection in the third step first, get the name of its db_link
Sql>drop Public Database link dbl_mesdb15. US. Oracle.com
Database connection Discarded

Oracle Database Link Usage Instructions