How to Create a DB Link for an Oracle database to operate a remote database

OracleOperationRemote databaseYou can useCreate a DB LinkThis document describes the creation process in detail. Now let's get started. Assume that the local IP address is 192.168.1.11, database instance orcl, User name: tysp, password: test Remote database IP Address: 192.168.1.52, database instance lntysp, User name: tysp, password: 12345678.

1. Create a DB Link to operate a remote database

Statement: create database link TEST_DBLINK.REGRESS.RDBMS.DEV.US.ORACLE.COM

Run the following command:

 
 

  
  
connect to tysp identified by "12345678"  
  
  
 
  
  
using '192.168.1.52/lntysp';  
 
 

In the base, TEST_DBLINK is the name of the DB Link we get. Oracle will automatically add the next letter,

Graph in PL/SQL:

2. After the database is created, you can operate the remote database.

1. Insert data:

 
 

  
  
Insert into p_sys_log @ TEST_DBLINK values ('123456', '1234568', 'title', sysdate, 'content', '1', '1234568 ');
  
  
 
  
  
Commit;
 
 

2. query data: select t. * from p_sys_log @ TEST_DBLINK t;

3. delete data:

 
 

  
  
delete from p_sys_log@TEST_DBLINK t where t.id='1111';  
  
  
 
  
  
commit; 
 
 

3. You can also create synonyms for DB Link to facilitate data operations.

Create or replace synonym TEST_DBLINK_SYNfor;

PL/SQL:

In this case, you can access the synonym to operate the remote database (the P_SYS_LOG table is used as an example ):

1. Add data:

 
 

  
  
Insert into TEST_DBLINK_SYN values ('20140901', '20160901', 'title', sysdate, 'content', '2', '20160301 ');
  
  
 
  
  
Commit;
 
 

2. query data: select t. * from TEST_DBLINK_SYN t;

3. delete data: delete from TEST_DBLINK_SYN t where t. id = '000000 ';

This article introduces how to create a database Link for Oracle databases to operate remote databases. I hope this introduction will bring you some benefits. Thank you!