Oracle DB : Create Database Link to access objects from a remote database.

Most often we have the databases on different servers. the transaction database and the traditional data-warehouse will always reside on different servers. Sometimes we might have to use the database objects from OLTP to load data into OLAP. This is most common when we use the Incremental Loads or Materialized views and the most common technique is to create a database link to enable users to access the objects from remote database.


Create DB Link

        CREATE PUBLIC DATABASE LINK 
        [LINK NAME]
        CONNECT to [USERNAME]
        IDENTIFIED BY 
        [PASSWORD]
        USING '[TNS NAME]'

for example if i have to connect to database on server myserver01 with DB details as below

Scott/tiger @ ORCL , orcl being the tns entry.

        CREATE PUBLIC DATABASE LINK
        MY_LINK
        CONNECT TO SCOTT
        IDENTIFIED BY tiger
        USING 'ORCL'

we can add the tns entry to tnsnames.ora file under location [DB_HOME]/NETWORK/ADMIN/


Now you can try accessing the object of remote database from your database by adding @DBlink at the end.

For example if you want to access employee table as per the above we use below statement.

        SELECT * FROM EMPLOYEE@MY_LINK