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
BEGIN
    EXECUTE IMMEDIATE 'CREATE DATABASE LINK database_link_name '
            ||'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: