Como Corrigir o Erro ORA-01775 Looping Chain of Synonyms no Oracle

Esse problema que pode causar muita dor de cabeça para DBAs: o erro ORA-01775 “looping chain of synonyms”. Esse erro ocorre quando há uma cadeia de sinônimos que aponta para si mesma ou apontamento inexistente. Para ajudar a resolver esse problema rapidamente, desenvolvi um script que identifica sinônimos inválidos.

Script para Identificação de Sinônimos Inválidos

Para facilitar você pode baixar o script synonyms_ORA-01775_looping_chain.sql

-- |
-- +-------------------------------------------------------------------------------------------+
-- | Objetivo   : Identificar Sinonimos invalidos erro ORA-01775: looping chain of synonyms    |
-- | Criador    : Roberto Fernandes Sobrinho                                                   |
-- | Data       : 18/06/2024                                                                   |
-- | Exemplo    : @synonyms_ORA-01775_looping_chain.sql <o: synonym_nme>                       |  
-- | Arquivo    : synonyms_ORA-01775_looping_chain.sql                                         |
-- | Referncia  :                                                                              |
-- | Modificacao: 1.0 - 18/06/2024 - rfsobrinho - Versao Inicial                               |
-- +-------------------------------------------------------------------------------------------+
-- |                                                                https://dbasobrinho.com.br |
-- +-------------------------------------------------------------------------------------------+
-- |"Estamos trabalhando para resolver seu problema, dentro dos limites da física e da lógica"
-- +-------------------------------------------------------------------------------------------+
SET TERMOUT OFF;
ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-YY HH24:MI:SS';
SET SERVEROUTPUT ON
EXEC dbms_application_info.set_module( module_name => 'd[synonyms_ORA-01775_looping_chain.sql]', action_name =>  'd[synonyms_ORA-01775_looping_chain.sql]');
COLUMN current_instance NEW_VALUE current_instance NOPRINT;
SELECT rpad(sys_context('USERENV', 'INSTANCE_NAME'), 17) current_instance FROM dual;
col p1 new_value 1
select null p1 from dual where 1=2;
select nvl( '&1','-1') p1 from dual ;
--define v_synonym_name=&1
SET TERMOUT ON;
PROMPT
PROMPT +-------------------------------------------------------------------------------------------+
PROMPT | https://github.com/dbasobrinho/g_gold/blob/main/synonyms_ORA-01775_looping_chain.sql      |
PROMPT +-------------------------------------------------------------------------------------------+
PROMPT | Script   : Lista Sinonimos Sem Apontamento                       +-+-+-+-+-+-+-+-+-+-+-+  |
PROMPT | Instancia: &current_instance                                     |d|b|a|s|o|b|r|i|n|h|o|  |
PROMPT | Versao   : 1.0                                                   +-+-+-+-+-+-+-+-+-+-+-+  |
PROMPT +-------------------------------------------------------------------------------------------+
PROMPT
SET ECHO         OFF
SET FEEDBACK     OFF
SET HEADING      ON
SET LINES        188
SET PAGES        300 
SET TERMOUT      ON
SET TIMING       OFF
SET TRIMOUT      ON
SET TRIMSPOOL    ON
SET VERIFY       OFF
SET SERVEROUTPUT ON
SET VERIFY       OFF

DECLARE
    v_exists INTEGER;
    v_synonym_name VARCHAR2(100);
BEGIN
    select UPPER(DECODE(LENGTH('&&1'), 0, NULL, '&&1')) into v_synonym_name from dual;

    FOR rec_syn IN (SELECT OWNER, SYNONYM_NAME, TABLE_OWNER, TABLE_NAME, DB_LINK
                      FROM DBA_SYNONYMS
                     WHERE OWNER NOT IN ('SYS', 'SYSTEM', 'AUDSYS', 'DBSNMP', 'XDB') 
                       AND TABLE_OWNER NOT IN ('SYS', 'SYSTEM', 'AUDSYS', 'DBSNMP', 'XDB')
                       AND DB_LINK IS NULL
                       AND (v_synonym_name = '-1' OR SYNONYM_NAME like '%'||v_synonym_name||'%')
                     ORDER BY TABLE_OWNER, OWNER)
    LOOP
        SELECT COUNT(*)
        INTO v_exists
        FROM DBA_OBJECTS
        WHERE OWNER = rec_syn.TABLE_OWNER
        AND OBJECT_NAME = rec_syn.TABLE_NAME;

        IF v_exists = 0 THEN
            DBMS_OUTPUT.PUT_LINE('SYNONYM ' || RPAD(rec_syn.OWNER || '.' || rec_syn.SYNONYM_NAME,40,' ') ||
                                 ' esta INVALIDO porque nao existe o objeto de apontamento ' ||
                                 rec_syn.TABLE_OWNER || '.' || rec_syn.TABLE_NAME);
		ELSIF v_exists > 0 AND v_synonym_name <> '-1' THEN
            DBMS_OUTPUT.PUT_LINE('SYNONYM ' || RPAD(rec_syn.OWNER || '.' || rec_syn.SYNONYM_NAME,50,' ') ||
                                 ' esta VALIDO! Objeto de apontamento ' ||
                                 rec_syn.TABLE_OWNER || '.' || rec_syn.TABLE_NAME);
        END IF;
    END LOOP;
END;
/
SET FEEDBACK  ON
SET VERIFY    ON
PROMPT
PROMPT

Como Utilizar o Script

Download: Baixe o script utilizando o link acima e salve-o em seu diretório de scripts.

Execução: Execute o script no SQL*Plus utilizando o comando abaixo, passando o nome do sinônimo como parâmetro (ou deixe em branco para verificar todos):

SQL > @synonyms_ORA-01775_looping_chain.sql <nome_do_sinônimo>

O parâmetro <nome_do_sinônimo> é opcional. Se não for especificado, o script verificará todos os sinônimos.

Interpretação dos Resultados: O script irá listar todos os sinônimos que estão inválidos devido à falta de referência do objeto de apontamento, o que causará o erro ORA-01775.
Aqui estão alguns exemplos de saída para sinônimos inválidos:

SQL > @synonyms_ORA-01775_looping_chain.sql

HR.EMPLOYEE_SYNONYM                 esta INVALIDO porque nao existe o objeto de apontamento HR.EMPLOYEES
HR.DEPARTMENT_SYNONYM               esta INVALIDO porque nao existe o objeto de apontamento HR.DEPARTMENTS
HR.JOB_SYNONYM                      esta INVALIDO porque nao existe o objeto de apontamento HR.JOBS
PUBLIC.EMPLOYEE_SYNONYM             esta INVALIDO porque nao existe o objeto de apontamento HR.EMPLOYEES
PUBLIC.DEPARTMENT_SYNONYM           esta INVALIDO porque nao existe o objeto de apontamento HR.DEPARTMENTS

Exemplo de Erro ORA-01775 em Ação

Exemplo prático de um usuário tentando acessar um sinônimo inválido e recebendo o erro ORA-01775:

SQL > SELECT * FROM HR.EMPLOYEE_SYNONYM;

ORA-01775: looping chain of synonyms

SQL > SELECT * FROM DEPARTMENT_SYNONYM;

ORA-01775: looping chain of synonyms

Exemplo de Erro ORA-01775 em uma Procedure

Se o objeto referenciado pelo sinônimo estiver sendo utilizado dentro de uma procedure, o erro também ocorrerá quando a procedure for criada ou executada.

SQL > CREATE OR REPLACE PROCEDURE test_proc AS
    v_emp HR.EMPLOYEES%ROWTYPE;
BEGIN
    SELECT * INTO v_emp FROM HR.EMPLOYEE_SYNONYM;
END;
/

SQL > SHOW ERRORS;

Errors for PROCEDURE TEST_PROC:

LINE/COL ERROR
-------- -----------------------------------------------------------------
3/5      PL/SQL: SQL Statement ignored
3/18     PL/SQL: ORA-01775: looping chain of synonyms

Como Corrigir o Problema

Recriar o sinônimo apontando para o objeto correto:

SQL >  CREATE OR REPLACE SYNONYM HR.EMPLOYEE_SYNONYM FOR HR.CORRECT_EMPLOYEES;

Synonym created.

Para Finalizar

Identificar sinônimos inválidos rapidamente é crucial para manter a integridade e a performance do seu banco de dados. Com este script, você pode facilmente detectar e corrigir problemas de sinônimos em loop. Isso não só melhora a estabilidade do ambiente, mas também previne falhas futuras ao garantir que todas as referências de sinônimos estejam corretas. Depois que o seu cliente quebrar o ambiente e você sacar rapidamente o script e achar o problema, deixará seu cliente feliz e saltitante!

🚀 #OracleDatabase #DBA #Configuração #GuinaNãoTinhaDó #CaceteDeAgulha #NinguémTemPaciênciaComigo #ClienteSemNoção #AjudaODBA #FeijãoComArroz #DBAHerói🚀

Compartilhe

Pesquisar

Roberto Sobrinho

Sou Roberto Fernandes Sobrinho, também conhecido como Sobrinho DBA , pós graduado em “Architecture and Database Administration”, entusiasta, dedicado e com 20 anos de experiência com Oracle Database e suas diversas distribuições e variações.