Based on the Oracle multi-database query method (SHARE), oracle database query and sharing

Based on the Oracle multi-database query method (SHARE), oracle database query and sharing

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;

The above multi-database query method based on Oracle (Sharing) is all the content shared by the editor. I hope to give you a reference and support for the customer's house.