How To Create Database Link

Prerequest:-

DBLink Name

Schema name and password

Connection string(tnsping SID)

Creating Database Link:-

Check DBLink is allreday existist or not

SQL> Select * from dba_db_links where db_link='db_link_name';

    

CREATE DATABASE LINK  
CONNECT TO  
IDENTIFIED BY  
USING 'TEST=(
    DESCRIPTION=(
        ADDRESS=(
            PROTOCOL=TCP
            (HOST=ebstest.vis.com)
            (PORT=1521)
        )
        (CONNECT_DATA=(
            SERVICE_NAME=test
            INSTANCE_NAME=test
        ))
    )
)';
    

If you want to recreate a DB Link first get the source code from the database link before you drop the DBLink

SQL>set long 1000

SQL> select DBMS_METADATA.GET_DDL('DB_LINK','DB.DBLINK,'DB.OWNER') from dba_db_links;

    

Connectivity Test:-

select * from global_name@DB_LINK_NAME

select * from global_name;

    

Drop DB Link:

Check the DB link and respective user and connect the user drop the db link 

SQL> set lines 1000

col owner for a20

col username for a20

col host for a40

col db_link for a30

col create for a10

SQL> select * from dba_db_links;

SQL> Select * from dba_db_links where db_link='D_LINK_NAME';

SQL> Drop database link ;