Create-Drop Database Link in Oracle

Step-1:

Login to PL/SQL Developer in Host DB where we want to create Database Link and execute below query to create:

CREATE  PUBLIC DATABASE LINK ora_std_link

CONNECT TO apps

IDENTIFIED BY password

USING '(DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=ora.oracle.com) (PORT=1521))(CONNECT_DATA= (SERVICE_NAME=ORACLE) (INSTANCE_NAME=ORACLEDEV)))';

NOTE:

Apps -> User name of Target/Remote Database

Password -> Password for above username of Target/Remote Database

In USING string give the Target/Remote instance tns entry from tnsentry.ora file in a single quote.

Step-2:

Run Query to check if DATABASE LINK was created:

select * from dba_db_links

create-drop-database-link-in-oracle

Step-3:

Run below Query from the host Database where DB Link was created to check if it is working and able to connect to Target/Remote database:

select sysdate from dual@ora_std_link.us.oracle.com ;

Step-4: To Drop DATABASE LINK:

DROP PUBLIC DATABASE LINK "ora_std_link.us.oracle.com" ;

NOTE : Check in table -> user_sys_privs if you have all the PRIVILEGE to CREATE/DROP database link.

create-drop-database-link-in-oracle

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s