Criando um dblink abaixo de outro usuário - Oracle 11g

Criar um dblink é uma tarefa comum e relativamente fácil no dia-a-dia de um DBA. Porém em alguns casos esta atividade pode se tornar um pouco mais "complicada".

Imagine o seguinte cenário, onde precisamos criar um DBLINK abaixo de um usuário ao qual não temos acesso. De acordo com a Oracle, não é possível criar um dblink em "outro" usuário sem acessa-lo.

Restriction on Creating Database Links 

You cannot create a database link in another user's schema, and you cannot qualify dblink with the name of a schema. Periods are permitted in names of database links, so Oracle Database interprets the entire name, such as ralph.linktosales, as the name of a database link in your schema rather than as a database link named linktosales in the schema ralph.)

Temos 2 opções para executar esta atividade. A primeira opção seria alterarmos temporariamente a senha do usuário e depois voltar a senha original (verificar neste link o processo). Assim seria possível conectar com o usuário que desejamos criar o dblink e efetuar sua criação.

Porém alguns ambiente podem ter restrições, não permitindo assim que possamos realizar a alteração da senha, mesmo que temporariamente. 

Nestes casos, podemos utilizar a segunda opção, que consiste em criar o dblink através de uma procedure.

Imagine que nosso usuário LAMIM precisa ter um dblink para a acessar os dados do usuário LAMIM2 na base TESTE.

Estarei primeiramente consultando na dba_db_links para mostrar que atualmente não temos nenhum dblink para o usuário LAMIM.

SQL> col DB_LINK for a45
SQL> col host for a40
SQL> set lines 210
SQL>  select * from dba_db_links where owner='LAMIM';

no rows selected

SQL>

Para atender essa necessidade, vamos inicialmente conceder permissão para que o usuário LAMIM possa criar uma procedure e dblink.

SQL> grant create procedure to lamim;

Grant succeeded.

SQL> grant create database link to lamim;

Grant succeeded.

SQL>

Agora vamos criar uma procedure abaixo do usuário LAMIM. Essa procedure será responsável por realizar a criação do dblink. Após cria-la, bastará executa-la para que o dblink seja criado abaixo do usuário LAMIM. Por fim, podemos remover a procedure dblink_to_user, uma vez que a mesma ja cumpriu seu papel.

SQL> create procedure lamim.dblink_to_user
  is
  begin
    execute immediate '
      create database link TESTE
      connect to lamim2
      identified by lamim2
      using ''TESTE'' ';
  end;
  /  2    3    4    5    6    7    8    9   10

Procedure created.

SQL> begin
  lamim.dblink_to_user;
end;
/
  2    3    4
PL/SQL procedure successfully completed.

SQL> drop procedure lamim.dblink_to_user;

Procedure dropped.

SQL>

Podemos consultar agora na dba_db_links e verificar que nosso dblink foi criado abaixo do usuário LAMIM.

SQL> col DB_LINK for a45
SQL> col host for a40
SQL> set lines 210
SQL> select * from dba_db_links where owner='LAMIM';

OWNER                          DB_LINK                                       USERNAME                       HOST                                     CREATED
------------------------------ --------------------------------------------- ------------------------------ ---------------------------------------- -------------------
LAMIM                          TESTE                                         LAMIM2                         TESTE                                    2017-05-03 13:22:40

SQL>

Fonte:https://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_5005.htm#SQLRF01205