ORACLE cross-Database Query Method

This article briefly describes how to create a databaselink database for cross-database ORACLE query. configure tnsnames for the local database server. add the following lines to the ora File $ vi $ ORACLE_HOMEnetworkadmintnsnames.ora, where DBLINK is the connection name (customizable), HOST and PORT are the IP address and PORT for database listening, and SERVICE_NAME is the number

This article describes how to create a database link to implement cross-database ORACLE query. configure tnsnames for the local database server. ora File $ vi $ ORACLE_HOME/network/admin/tnsnames. add the following lines to ora, where DBLINK is the connection name (customizable), HOST and PORT are the IP address and PORT for database listening, and SERVICE_NAME is

This article describes how to create a database link to implement cross-database ORACLE query.

1. Configure the tnsnames. ora file of the Local Database Server

$ Vi $ ORACLE_HOME/network/admin/tnsnames. ora

Add the following lines, where DBLINK is the connection name (customizable), HOST and PORT are the IP address and PORT for database listening, and 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 on to the local database and create the database link

Execute the following query statement. MEDIADB is the database link name (customizable), and MEDIADBLINK is the connection name defined in tnsnames. ora,
Dbuser is the user name and password

-- Create database link
Create database link MEDIADB
Connect to dbuser identified by password
Using 'mediadblink ';

Note: the correctness of the user name and password will not be verified here

3. Use the Linked database

3.1 Data Query, deletion, and insertion are the same as local databases, except that the table name must be written as "table name @ database link name", as shown in figure

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 that of the 3.1 statement.

Select * from aaa;

The statement for deleting synonyms is

Drop synonym aaa;