Steps to create private db_link even if you don't have schema password

Sometimes we get request to create a private db_link in some application schema and chances are we don’t know schema password to connect to the application schema. As oracle will not allow even a user with DBA privileges to create private db_link in another schema without directly login as that user, here is a short cut to create a private db_link in those scenarios.

I assume that you have entries in tnsnames.ora in source database  and you can connect to target database from source database using sqlplus.  In this example, source database name is APPLE and target database name is ORANGE and domain name is tarikida.com. We should grant “CREATE DATABASE LINK” privilege to the user who want DB_LINK. It is MDRADM in this example.

Create a sql script as follows and execute it as a DBA user.

spool crt_app_dblinks.log;

grant CREATE DATABASE LINK to MDRADM;

-- rem Creating ORANGE_RO_LINK.tarikida.com LINK for User: MDRADM

create or replace procedure MDRADM.madhu1

   is

   begin

      execute immediate '

        create database link ORANGE_RO_LINK.tarikida.com

        connect to CMS_RO identified by passw0rd

        using ''ORANGE.tarikida.com'' ';

    end;

/

exec MDRADM.madhu1;

/

drop procedure MDRADM.madhu1;

revoke CREATE DATABASE LINK from MDRADM;

spool off;