DB Link

The DB link mechanism goes through TNS, so just define a TNS entry for your local database and use that in your link.

tnsnames.ora (client)

dblink =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = remotehostname)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SID=DRPROC)

    )

  )

$tnsping dblink

listener.ora (server)

DRPROC =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = remotehostname)(PORT = 1521))

  )

You can use Database Link (dblink) to execute SQL against external database as if it was the database you are logged in to. For example, you might need to transfer sales information from a company branch located in DRPROC.

Private DBLink

user> create database link <user.link_name> connect to <remote_user> identified by <password> using '<remote_sid>';

To check what privileges are granted.

scott> select * from session_privs;

SQL> grant create database link to scott;

e.g

scott> create database link scott.drlink connect to neeraj identified by neeraj using 'drproc';

scott> select * from dual@drlink;

scott> select sysdate from dual@drlink;

scott> select * from tab@drlink;

Public DBLink

scott> create public database link scott.drlink connect to neeraj identified by neeraj using 'drproc';

Now you can execute SQL statements using the remote database. To specify a table from the remote database, you specify the table name and the database link name: <table_name>@<dblink_name>.

SQL> select * from table_name@dblink_name;

For example, to select employees from the DCPROC database using a database link with name drlink, you can use the following SELECT SQL:

      

scott> select * from emp@drlink;

      

To remove an existing Oracle database link, you use the DROP DATABASE LINK command:

scott> drop database link <link_name>;     

scott> drop database link drlink;

      

Example 1: To retrieve sales information for the Miami office:

1. Create a new dblink to the Miami Office’s database.

the create database link statement creates a schema object in one database that enables you to access objects on another database.

create database link drlink  

connect to neeraj identified by neeraj

using 'drproc';

In the example above, user neeraj on the remote database (drproc) defines a fixed-user database link named drlink to the neeraj schema on the local database.

2. Copy employees information from today:

insert into emp select * from emp@drlink where sales_date >=trunc(sysdate, 'dd');

commit;

3. Remove the database link:

drop database link drlink;

select * from dba_db_links;

Dictionary:

dba_db_links - all db links defined in the database

all_db_links - all db links the curernt user has access to

user_db_links - all db links owned by current user