How to create database link in oracle

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 = ora-data.blogspot.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 ORA-DATA.blogspot.com
connect to username identified by password
 using 'ORA-DATA';

Database link created.

Note: We need to use password for target instance (i.e. If we need to create DB link from abc to xyz on abc instance then we need to use apps password for xyz.

If there is mentioned OWNER, then we need to connect with that user and then create db links.

SQL> select owner||','||db_link||','||username||','||host||','||from dba_db_links;
OWNER||','||DB_LINK||','||USERNAME||','||HOST
---------------------------------------------
PUBLIC,ORA-DATA.BLOGSPOT.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 = ora-data.blogspot.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=ora-data.blogspot.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@ORA-DATA.BLOGSPOT.COM;
SYSDATE
---------
07-JAN-17

Some more useful links:
Compile JSPs Manually Steps and Concepts
R12.1 Vs R12.2 Technical
R12.2 Architecture and Concepts
Interviews Questions and Answers
Important SQL Scripts
Hope this is useful and helpful, please let us know for any more details and information.