How to create a DBlink in oracle database

by

In this blog I shared the command to check the details of existing DBlink and create the new DBlink in oracle database.

col owner format a20
col DB_LINK format a50
col host format a30
set lin 200
set pagesize 200
select owner,db_link,host from dba_db_links;

select owner,db_link,host from dba_db_links where db_link='<DBLINK_NAME> ';

select username,account_status from dba_users where username='<SCHEMA_NAME>';

Add TNS entry in tnsnames.ora file for the server which you want to access.

testdb =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = testserver01.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = testdb)
    )
  )

Check the DDL of existing DBlink’s

set long 999999
select dbms_metadata.get_ddl('DB_LINK', '<DBLINK_NAME>', 'PUBLIC') from dual;

Create the DBlink

Create public database link <DBLINK_NAME> connect to <schema_name> identified by <password> using 'TESTDB';

Check the status of DBlink.

select sysdate from dual@<DBLINK_NAME>;

or 

select name from database@<DBLINK_NAME>;

Drop the DBlink.

drop public database link <DBLINK_NAME>;

Leave a Comment