Managing Database Links

          A database link enables a one-way connection to a remote database from a local database. The link is one way only. The remote database users can’t use this link to connect to the local database—they must create a separate database link for that. A database link allows you to gain access to a different database though a remote database user account, you don’t have to be a user in the remote database. Your privileges on that database will be identical to the privileges of the user account you use when creating the database link. Database links are useful when you want to query a table in a distributed database or even insert data from another database’s table into a local table. Database links allow users to access multiple databases
as a single logical database.

Creating Database Link
1) Create and start listner on Target database (prod)
2) Create tnsnames.ora on source database. (test)
Creating a Private Database Link
             A private database link is owned by the user that creates the link. In the following statement, the
SYSTEM user creates a private database link. The database link enables a connection to the remote
database using the hr user’s username and password in that database.

SQL> create database link test
2 connect to hr identified by hr
3 using 'TO_TEST';

Database link created.
(Where TEST is a Link name and 'TO_TEST' is a name of tnsnames.ora)

After the link is created, the SYSTEM user can query the hr  tables in the remote database.

SQL> select * from tab@test;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
DEPT TABLE
EMP TABLE
BONUS TABLE
SALGRADE TABLE
HR TABLE
Insert into Table in the Remote Database:-
SQL> insert into table emp@test as select * from emp;

Delete from Table in the Remote Database:-
SQL> delete from table emp@test where job='CLERK';

Creating a Public Database Link
      A public database link, unlike a private database link, enables any user or any PL/SQL program unit to access the remote database objects. The creation statement is very similar to that for a private database link. You just add the PUBLIC keyword to the CREATE DATABASE LINK statement:

SQL> CREATE PUBLIC DATABASE LINK pub
2 CONNECT TO hr IDENTIFIED BY hr
3 USING 'TO_TEST';
Database link created.
(Where PUB is a Link name and 'TO_TEST' is a name of tnsnames.ora)

Note:- You can create a public database link if several users require access to a remote Oracle database from a local database. Otherwise, create a private database link, which will allow only the owner of the private database link to access database objects in the remote database.
Once the public MONITOR link is created, any user can log in to a remote database using that link. In the following example, the user tester uses the public database link to query the remote database.
SQL> CONNECT zee/zee;
Connected.
SQL> SELECT COUNT(*) FROM hr.emp@pub;
COUNT(*)
----------
65
Insert into Table in the Remote Database
SQL> insert into table emp@pub values(2,'sam','India');

Delete from Table in the Remote Database
SQL> delete from table emp@test where job='CLERK';

GLOBAL_NAMES
global_names (required to be TRUE for replication. If the value of the GLOBAL_NAMES initialization parameter is TRUE, then the database link must have the same name as the database to which it connects.

Close Database Link:-
SQL> ALTER SESSION CLOSE DATABASE LINK test;

You can query about the Public and Private database links:-
SQL> select * from all_db_links;

SQL> select * from dba_db_links;

Drop database links:-
private:-
SQL> drop database link test;
Public:-
SQL> drop public database link pub;