DATABASE LINK IN ORACLE

  

My developer requested for creating a Db-Link between two databases of the same server.

• Database link is nothing but a pointer in a local database which lets you to access the objects of remote database through application.

 Types of Db-Links :-

• Private : means it can only be access by the user that created the database link.
• Public : means all the database users can access that link. 

 Environment:- Oracle 11g  on Windows 2008 R2

    

Steps:-

                                                                                         

D:\app\oracle\product\11.2.0\dbhome_1\NETWORK\ADMIN> tnsping tohr

TNS Ping Utility for 64-bit Windows: Version 11.2.0.3.0 - Production on 03-JUN-2015 11:31:58

Copyright (c) 1997, 2011, Oracle.  All rights reserved.

Used parameter files:

D:\app\oracle\product\11.2.0\dbhome_1\network\admin\sqlnet.ora

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = ORADB3)(PORT = 1599))) (CONNECT_DATA = (SERVICE_NAME = hr)))

OK (0 msec)

• In 11g its very simple only we have to mention hostname,port and tns service name of remote database.

• mmmlink is my dblink name
• FMIS is the schema of remote database 
• Hr is tns service name 
• ORADB3.iecc.md is the hostname of my target database
• 1521 is the database network port of remote database.

  

SQL> create database link mmmlink connect to fmis identified by fmis using 'ORADB3.iecc.md:1521/hr';

Database link created.

Now check the db-link its working or not

SQL> select * from hremp@mmmlink;

     EMPNO     ENAME

---------- -----------------------

       155           SONY

       156           MOHAMMED

Happy Learning…..