DOYENSYS Knowledge Portal

We Welcome you to visit our DOYENSYS KNOWLEDGE PORTAL : Doyensys Knowledge Portal

Tuesday, July 5, 2016

Steps to create database link in other schema

Steps to create db link in other schema

Below is a method to create private database link in other schema

As sys user create the following procedure under the schema for which database link has to created

CREATE PROCEDURE schema.create_db_link AS
            ||'CONNECT TO USER_name IDENTIFIED BY password '
            ||'USING ''tns_name_for target_db''';
END create_db_link;

now execute the procedure as below,

exec schema.create_db_link;

the required database link is created under the schema.

you can check using the below query

select owner,db_link,to_char(created,'dd-mon-yyyy:hh24:mm:ss') from dba_db_links 
where owner='&schema_name';

Now we can drop the procedure.

drop procedure schema.create_db_link;

Hope this helps ...

No comments: