Como criar um DBLINK privado para outro esquema

Hoje me surgiu a necessidade de criar um dblink privado em um esquema especifico, como sabemos o Oracle Database não permite que um usuário crie um dblink privado no esquema de outro usuário, como ninguém sabia a senha do esquema que iria usar o dblink, criei o artificio abaixo para criar um dblink privado no esquema USR_CONSULTA.

Verifiquei os dblinks existentes:

SET LINESIZE 300
COLUMN owner FORMAT A30
COLUMN db_link FORMAT A30
COLUMN username FORMAT A30
COLUMN host FORMAT A50
alter session set nls_date_format = 'DD/MM/YYYY HH24:MI:SS';
SELECT owner,
       db_link,
       username,
       host,
	   CREATED
FROM   dba_db_links
ORDER BY owner, db_link;

Para criar um dblink privado DB_LINK2 no esquema USR_CONSULTA, criei a procedure no esquema USR_CONSULTA com grant de create database link, este é o pulo do gato usando o execute immediate:

create or replace procedure USR_CONSULTA.P_DBLINK
is
begin
execute immediate 'create database link DB_LINK2 connect to "consult" identified by "12345678" using ''DB01''';
end;
/

grant create database link to USR_CONSULTA;

Executando a procedure criada com o usuário SYS:

exec USR_CONSULTA.P_DBLINK;

Verificando o dblink criado:

SET LINESIZE 300
COLUMN owner FORMAT A30
COLUMN db_link FORMAT A30
COLUMN username FORMAT A30
COLUMN host FORMAT A50
alter session set nls_date_format = 'DD/MM/YYYY HH24:MI:SS';
SELECT owner,
       db_link,
       username,
       host,
	   CREATED
FROM   dba_db_links
ORDER BY owner, db_link;

OWNER             	DB_LINK     USERNAME       HOST               CREATED
-------------------	----------- -------------- ------------------ ----------------------
USR_CONSULTA       	DB_LINK2    consult        DB01               19/03/2024 14:30:25

Apagando a procedure e removendo o grant:

revoke create database link from USR_CONSULTA;
drop procedure USR_CONSULTA.P_DBLINK;

Se você precisar apagar o dblink privado, segue a procedure para realizar o drop:

create or replace procedure USR_CONSULTA.P_DBLINK_D
is
begin
execute immediate 'drop database link DBL_CONSULTA_CORPORATIVO';
end;
/
-- GRANT 
grant create database link to USR_CONSULTA;
-- EXECUTE PROCEDURE DROP DBLINK
exec CONSULTA_CORPORATIVO_DBL.P_DBLINK_D;

-- REVOKE
revoke create database link from USR_CONSULTA;
-- DROP PROCEDURE
drop procedure USR_CONSULTA.P_DBLINK_D;

Espero que esta dica possa te ajudar.