DBAs usually don't have access to schema passwords. We can only see an encrypted version and that's the way it should be. But from time to time we are asked to create a Database Link. Database Links should never be public because other users in the database can also have access to it, and by extension, to all objects in the remote database.

Database Links cannot be created in other user's schema, even if you are a DBA. For example, a "create database link [schemauser].[dblink_name] ...." will give you an error. So usually what DBAs do is finding out the schema password (which in most cases should not have) or worst, resetting it. Resetting the password is not a bad idea, since you can return to the old password if you save the hash of the original value and then issue an "alter user [schemauser] identified by values '[hash_value]';". But this has a problem: when you are in a live environment you cannot change a password without interrupting sessions, and this is unacceptable even if it is for only a few seconds.

In this cases I like a cleaner approach: create a package in the schema user that will later create the database link using dynamic SQL. Let's go to the example.

(use Sqlplus, or adapt it for your client)

First let's grant a create database link (a not very common privilege that is not usually granted to typical users). Then we create the procedure that will do the "magic" (replace OWNER_NAME for the future owner of the dblink):

grant create database link to OWNER_NAME;  

create or replace procedure OWNER_NAME.e(p_sql varchar2)
is
begin
    execute immediate p_sql;
end;
/  

Now lets create the database link. Replace LINK_NAME, USER_NAME, USER_PASSWORD and TNS_ENTRY for the ones that apply in your case:

exec OWNER_NAME.e('CREATE DATABASE LINK "LINK_NAME"  CONNECT TO  "USER_NAME" IDENTIFIED BY "USER_PASSWORD"  USING ''TNS_ENTRY''');

That should have created the DB link. We can create a second proc to validate if it's working:

reate or replace procedure OWNER_NAME.ee is
v_pp varchar2(30);
begin
   select 1 into v_pp from dual@link_name;
   dbms_output.put_line('pp = '||v_pp);
end;
/
 
set serveroutput on
 
exec OWNER_NAME.ee

You should see "pp=1" when you run the "ee" proc. If you have any error, then work it out! Usually TNS errors are the typical ones (verify you have the appropriate tns entry in your server's tnsnames.ora file). Finally let's clean all:

revoke create database link from OWNER_NAME;

drop procedure OWNER_NAME.e;

drop procedure OWNER_NAME.ee;

That's all!