Oracle Cross-database Query method __ Database

This paper describes the method of Oracle cross-database query through the creation database link

1. Configure the local database server's Tnsnames.ora file

$vi $ORACLE _home/network/admin/tnsnames.ora

Add the following line, where Dblink is the connection name (customizable), and the host and port are the IP and ports that are listening for the database, Service_Name is the SID of the database.

Mediadblink =
(DESCRIPTION =
(Address_list =
(address = (PROTOCOL = TCP) (HOST = 10.0.0.1) (PORT = 1521))
)
(Connect_data =
(service_name = db)
)
)

2. Log in to the local database, create the DB link

Execute the following query, where MEDIADB is the database link name (customizable) Mediadblink the connection name previously defined in Tnsnames.ora.
Dbuser for username, password for password

--Create Database link
Create DATABASE link mediadb
Connect to Dbuser identified by password
Using ' Mediadblink ';

Note: This does not verify the correctness of the username password

3. Using a linked database

3.1 Querying, deleting, and inserting data is the same as manipulating a local database, except that the table name needs to be written as "table name @database link Name", as

SELECT * from TABLE_NAME@MEDIADB;

3.2 You can also create a synonym for this table

Create synonym AAA for table_name@mediadb;

The effect of the following statement is the same as in 3.1

SELECT * from AAA;

The statement that deletes the synonym is

Drop synonym AAA;