How to create database link in oracle 11gr2

                                 Database link Details:

Database link is a schema object in one database which enables us to access objects

 on another databases.And the other databases not need to be an Oracle Database

 system.But here, to access non-Oracle systems we should use Oracle different 

Services.

Then we have created a database link after that we can use it to refer to tables 

and views on the other databases. As in SQL statements, we can refer to a table

 or view on our other database by appending @dblink to the table or view name.

 And we can query a table or view on the other database with the SELECT 

command. We can access remote tables and views using any of INSERT, 

UPDATE, DELETE, or LOCK TABLE DML command.

There are some below points which we should notice before starting creation 

of DB link:

1. For private database link:

    We should have the CREATE DATABASE LINK system privilege.

2. For public database link:

   We should have the CREATE PUBLICDATABASE LINK system privilege. And   

also, we must have the CREATE SESSION system privilege on the remote Oracle

 database.

And oracle Net also must be installed on both the local and remote Oracle

 databases

Please follow the below steps to DBLINK Creation :

User/Password: abc/abc

TNS Setting:

ORA-DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = MDSHOEBDBABLOGSPOT.COM)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ora-data)
(SRVR = DEDICATED)
)

Now, Check the Instance details:

SQL> select open_mode,name from v$database;
OPEN_MODE NAME
---------- ---------
READ WRITE SKU

SQL> create public database link MDSHOEBDBABLOGSPOT.COM
connect to username identified by password
 using 'ORA-DATA';

Database link created.

SQL> select owner||','||db_link||','||username||','||host||','||from dba_db_links;
OWNER||','||DB_LINK||','||USERNAME||','||HOST
---------------------------------------------
PUBLIC,linux1.oracle.com,ABC,ORA-DATA,
 Now, we will check the working status of dblink.

$ tnsping ORA-DATA

Server, date and time details
Copyright (c) 1997 Oracle Corporation. All rights reserved.
Used parameter files:
/u01/oracle/ora-data/admin/ora-data/sqlnet_ifile.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS =

 (PROTOCOL = TCP)(HOST = linux1.oracle.com)(PORT = 1521))) 

(CONNECT_DATA = (SID=ORA-DATA))).

Now, We can see TNSPING is working is working fine.

$ tnsping ORA-DATA
TNS Ping Utility for date and time
Server, date and time details

Used parameter files:
/u01/oracle/ora-data/admin/ora-data/sqlnet_ifile.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=MDSHOEBDBABLOGSPOT.COM)(PORT=1521)) (CONNECT_DATA= (SID=ORA-DATA)))
OK (10 msec)
$

Now, we can check the dblink that it is working fine.
 

SQL> select sysdate from dual@MDSHOEBDBABLOGSPOT.COM;
SYSDATE
---------
07-JAN-17

Hope this is useful and helpful, please let us know for any more details and information.

 These are all from real life time experiences. We like to hear from you.

Thanks,