Sometime as a DBA you’ll need to (re)create a private database link in another schema. If you have that schema’s password, it’s pretty straight forward; grant create database link to the schema, connect as the schema, create the dblink, and revoke the grant from the schema.
However, if you don’t have the schema password for whatever reason, it’s not as straight forward as you would think! In this case, you can use the following code snippet to create a procedure in the schema that takes care of creating the dblink for you. Check the code and you’ll know what I mean… just update the anotherschema to the schema name that needs the dblink, and replace all the <placeholders> as you need.
Hope this helps!
--------
-------- Create DB Link
--------
CREATE OR REPLACE PROCEDURE anotherschema.crt_dblink
IS
BEGIN
execute immediate 'DROP DATABASE LINK <dblink>';
execute immediate '
create database link <dblink>
connect to <remoteuser>
identified by <remotepassword>
using ''<remotehost>'' ';
END;
/
BEGIN
anotherschema.crt_dblink;
END;
/
--------
-------- test DB Link
--------
CREATE OR REPLACE PROCEDURE anotherschema.tst_dblink
IS
l_output varchar(10);
BEGIN
-- check db_link
SELECT * INTO l_output FROM dual@"<dblink>";
-- print output
dbms_output.put_line( 'l_ouput is: ' || l_output );
END;
BEGIN
anotherschema.tst_dblink;
END;
/
--------
-------- Drop test procedures
--------
DROP PROCEDURE anotherschema.tst_dblink
/
DROP PROCEDURE anotherschema.crt_dblink
/
