Sinónimos Huérfanos en Oracle

Los objetos tipo sinónimo (tanto públicos como privados) en la base de datos son alias a otros objetos de base de datos. Estos objetos a los cuales se hace referencia en Oracle, tienen la peculiaridad de que pueden existir en la Base de datos o no.

Otro caso que se puede dar es que también es posible crear un sinónimo sobre un objeto existente en ese momento y posteriormente, eliminar el objeto referenciado. También podría darse el caso de que el objeto referenciado cambie de nombre, dejando al sinónimo apuntando a su nombre anterior. Esto dejará en base de datos lo que se llama «sinónimos huérfanos». Esto es debido a que esta referencia se guarda en una de las tablas diccionario de Oracle, pero al cambiar el objeto no se actualiza automáticamente.

Es recomendable revisar los sinónimos en Oracle que pueda tener un objeto cuando se modifica o se elimina. Pero si se nos pasa algún sinónimo huérfano, aplicando lo siguiente se pueden eliminar. La siguiente consulta retornará la cantidad de sinónimos huérfanos existentes en este momento en la base de datos.

SELECT S.TABLE_OWNER, COUNT(*) AS CANTIDAD 
FROM ALL_SYNONYMS S 
WHERE NOT EXISTS(SELECT 1 FROM ALL_OBJECTS O WHERE S.TABLE_OWNER=O.OWNER AND S.TABLE_NAME=O.OBJECT_NAME ) 
  AND OWNER NOT IN ('SYS','SYSTEM') 
GROUP BY S.TABLE_OWNER;

Lanzando el siguiente script, el cual está basado en la consultar sinónimos como lo acabamos de hacer, eliminará los sinónimos huérfanos que existan en la base de datos Oracle con la que estemos trabajando.

BEGIN 
  FOR V IN (SELECT DECODE (OWNER,'PUBLIC','DROP PUBLIC SYNONYM '||SYNONYM_NAME,'DROP SYNONYM '||OWNER||'.'||SYNONYM_NAME)||'' AS VSQL 
            FROM ALL_SYNONYMS S 
            WHERE NOT EXISTS(SELECT 1 FROM ALL_OBJECTS O WHERE S.TABLE_OWNER=O.OWNER AND S.TABLE_NAME=O.OBJECT_NAME ) 
              AND OWNER NOT IN ('SYS','SYSTEM')) LOOP 
    BEGIN
      EXECUTE IMMEDIATE V.VSQL;
    EXCEPTION 
      WHEN OTHERS THEN 
        DBMS_OUTPUT.PUT_LINE(V.VSQL||'-'||SQLERRM); 
    END; 
  END LOOP; 
END;