Oracle cross database query method

Time:2020-1-22

Execute the authorized dbuser account on the local database side of Oracle

SQL> grant create database link to dbuser;

1. Configure the tnsnames.ora file of the local database server


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

Add such as the following line, where dblink is the connection name (customizable), host and port are the IP and port that the database listens on, 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 in to the local database and create database link

Execute the following query statement, where mediadb is the database link name (customizable), and mediablink is the connection name previously defined in tnsnames.ora,
Dbuser is the user name and password is the password


 -- Create database link 
 create database link MEDIADB 
 connect to dbuser identified by password 
 using 'MEDIADBLINK'; 

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

3. Use linked database

3.1 querying, deleting and inserting data are the same as operating the local database, except that the table name needs to be written as “table name @ database link name”, such as


select * from [email protected] ; 

3.2 you can also create a synonym for this table


create synonym aaa for [email protected] ; 

The following statements work the same as in 3.1


select * from aaa; 

The statement to delete synonyms is


drop synonym aaa; 

The following is the supplement of other netizens:

Execute the authorized dbuser account on the local database side of Oracle

SQL> grant create database link to dbuser;

Configure the tnsnames.ora file of the local database server
$ vi $ORACLE_HOME/network/admin/tnsnames.ora

Add the connection configuration that requires remote connection server, such as:


ORCL_REMOTE =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oradb )(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)

Log in to the local database and create database link

Execute the following query statement, where orcl? Link is the database link name (customizable), orcl? Remote is the connection name previously defined in tnsnames.ora,
Dbuser is the user name and password is the password


create database link ORCL_LINK connect to dbuser identified by password using 'ORCL_REMOTE';

Query 2 ways to create database link:

1) , execute SQL statement.
Select * from user? DB? Links; — user DB link
select * from dba_db_links; –dba DB Link
Select * from V $dblink; — current DB link

2) In PL / SQL, click database links in the left browser to see the database links.

Use linked database

Querying, deleting and inserting data are the same as operating the local database, except that the table name needs to be written as “table name @ database link name”, such as


select * from [email protected]_LINK

Other:

Delete database link (orcl link in this case)
SQL> Drop database link ORCL_LINK;