Create and test Oracle database links

Create and use Oracle DBLINK

How to create? How to use it?

Describes how to create and use Oracle database links.

A simple application scenario for Oracle DBLINK

If there are two Oracle databases, one day the business needs to make the data of one of the database tables compare and check with the data of another database table. This takes two steps:

Create DBLINK

Write related logic check SQL

Related code

1. -- Create a public link
2. CREATE PUBLIC DATABASE LINK TESTDBLINK
3. Connect to remoteuser identified by REMOTEUSER_PW
4. Using'(DESCRIPTION =
5. (ADDRESS_LIST =
6. (ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.0.1) (PORT = 1521 ))
7. )
8. (CONNECT_DATA =
9. (SERVICE_NAME = ORCL)
10. )
11. )';
12. -- Test
13. Select count (*) FROM PUBLIC_USER STD_L where not exists (
14. SELECT 1 FROM PRIVATE_USER @ TESTDBLINK STD_R WHERE STD_R.USER_CODE = STD_L.USER_CODE
15. )
16. -- Kill Link
17. DROP PUBLIC DATABASE LINK TESTDBLINK