Oracle Database connection links

Server-1-Ip -192.168.1.15
Database 1-Name - Orcl 
User_Name - User2---password user2
Table_Name - Emp


Server-2-Ip -192.168.1.8 
Database 2-Name - cds
User_Name - User1----password user1
Table_Name - Dept 

Issue- user user2 on orcl database want to access table dept that is in user1 schema on cds database.


Solution:-
Step 1-On Orcl Database Grant create database link to User2 
Ex- SQL> grant create database link to user2;
                 Grant succeeded. 

Step 2- Now Connect to user user2 on orcl
SQL>conn user2/user2 


Step 3-Now create a database link in user2 schema.


Ex-
SQL> create database link dblink connect to user1 identified by user1 using 'cds'; 
          Database link created. 

Step 4- access the table like this.
Ex- 
SQL>select a.ename,b.dname from emp a,user1.dept@dblink b where a.deptno=b.deptno;

Make The Entery In tnsname.ora file at server1 running orcl database like this  

Ex- 
CDS =
          (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.8)(PORT = 1521))
          (CONNECT_DATA = 
          (SERVER = DEDICATED)
           (SERVICE_NAME = cds)
         ) 
  )

Make The Entery In listener.ora file at server2 running cds database and reload the listener like this 
 Ex-


SID_LIST_LISTENER =
     (SID_LIST = 
        (SID_DESC = 
        (SID_NAME = cds)
       (ORACLE_HOME = /opt/oracle/product/10.1.0/db_1)
       # (PROGRAM = extproc) 
  )
 ) 

LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1)) (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.8)(PORT = 1521)) ) ) 

Cmd>lsnrctl reload ---If require

People who read this post also read :

Posted in: Database Connection