Monday, April 2, 2018

Database LINK Creation (db_link)



If we need to create Database link. If both database is Oracle.




Prerequisites

To create a private database link, you must have the CREATE DATABASE LINK system privilege. To create a public database link, you must have the CREATE PUBLIC DATABASE LINK system privilege. Also, you must have the CREATE SESSION system privilege on the remote Oracle database.

Oracle Net must be installed on both the local and remote Oracle databases.



step-1) Make TNS entry in source machine of Target Machine.

Step-2) Please make sure Tns is pinging or not and connect using SQL prompt for remote database.



Assume-

Source IP 192.168.10.2

Destination IP 192.168.10.3 

Schema on Destination - Apple

Password for schema apple is same means "apple"




Step-3) Create db link

create database link DB_LINK_NAME connect to Remote_Schema identified by Remote_schema_passwd using 'Tns_alias';

create database link DB_LINK_NAME connect to APPLE identified by apple using 'APPLE.WORLD';

Step-4) Check from Source-


select count(*) from APPLE.table_name@DB_LINK_NAME;