How to make a reference to table in different database in Oracle

It can be very often than data are located in different databases, for example, HR data can be provided in some view which was created HR team. But it’s not very useful to use different databases from one application. The solution is to create DB Link.

create database link "HR_DATA.WORLD"
connect to USER_NAME
identified by "Password"
using '(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(PORT=1234)(HOST=hr.data.domain.com)))(CONNECT_DATA=(SERVICE_NAME=HRDATAP1)))';

and now you can run this types of queries from your database to external database in Oracle

select * from hr_data.table_name@HR_DATA;

But it’s difficult to type this long DB Link name, so it’s better to create synonym

CREATE PUBLIC SYNONYM HR
   FOR hr_data.table_name@hr_data;

so now this select statement will work from your database

select * from HR;