A database link is an schema object where you can access objects on another database. Database link is created in one database which helps you to access table/objects from on another database.
STEPS FOR CREATING DATABASE LINK
First : Before creating database you must remember service name which is located at tnsnames.ora. Here inside tnsnames.ora . Here, ORCL is my service name which need to mentioned while creating database link. Now lets go through step TWO.
[grid@storage admin]$ more tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/11.2.0/grid/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
(ADDRESS = (PROTOCOL = TCP)(HOST = storage.castrading.com)(PORT = 1521))
(SERVICE_NAME = orcl)
Second : Make Sure the user where you want to create database link has privilege to create database link. Suppose if you want to check the privilege for HR user then first login to hr user and then checked the PRIVILEGE FOR CREATE DATABASE LINK. Below output shows that HR user has privilege to create database link
HOW TO CHECK PRIVILEGE FOR CREATE DATABASE LINK ?
SELECT * FROM SESSION_PRIVS;
What if you don't have privilege to create database link ?
Answer : If you don't have privilege to create database link then you can follow the following steps :-
sqlplus / as sysdba --->LOGIN AS SYSDBA
grant create database link to hr;
exit and connect to hr.
THIRD : Now before creating database link lets take an example of tables for SCOTT user. From the below screen shot you can see that SCOTT user have four tables BONUS, DEPT,EMP and SALGRADE. Now lets assume like you want to access s all SCOTT USER tables from hr end. For this please follow steps FOUR.
FOUR : Login to HR use and trigger following query. At SQL 1 you edulink is the database link name that is used while connecting to scott user and ORCL is the service name taken from tnsnames.ora i.e. mentioned above at STEP FIRST. So once you create database link you can check/confirm whether you can access SCOTT table from HR user or not ? and for that i trigger query mentioned below at SQL 2. You can see SCOTT table named DEPT from HR user end which means database link is working. Same like wise you can check the other tables for SCOTT user from HR user end.
SQL 1> create database link edulink connect to scott identified by tiger using 'orcl';
Database link created.
SQL 2> select * from dept@edulink;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
FIFTH : If you want to check the list of database link under hr user then you can trigger following query :
select * from user_db_links;
Same likewise you can trigger following query from SYS user end
select * from dba_db_links where username='USERNAME';