Oracle Database Database Links Create Database Link
Example
Let we assume we have two databases "ORA1" and "ORA2". We can access the objects of "ORA2" from database "ORA1" using a database link.
Prerequisites:
For creating a private Database link you need a CREATE DATABASE LINK privilege.
For creating a private Database link you need a CREATE PUBLIC DATABASE LINK privilege.
*Oracle Net must be present on both the instances.
How to create a database link:
From ORA1:
SQL> create <public> database link ora2 connect to user1 identified by pass1 using <tns name of ora2>;
Database link created.
Now that we have the DB link set up, we can prove that by running the following from ORA1:
SQL> Select name from V$DATABASE@ORA2; -- should return ORA2
You can also access the DB Objects of "ORA2" from "ORA1", given the user user1 has the SELECT privilege on those objects on ORA2 (such as TABLE1 below):
SELECT COUNT(*) FROM TABLE1@ORA2;
Pre-requistes:
- Both databases must be up and running (opened).
- Both database listeners must be up and running.
- TNS must be configured correctly.
- User user1 must be present in ORA2 database, password must be checked and verified.
- User user1 must have at least the
SELECTprivilege, or any other required to access the objects on ORA2.
