[Oracle] 시노님(Synonym)의 유효성을 점검하는 방법

시노님(Synonym)은 데이터배이스 링크(Database Link)와 마찬가지로 생성 시점에는 논리적 연결만 정의하고, 실제 참조 시점(Runtime)에 대상 객체의 존재 여부를 확인합니다. 즉 생성 시점에 DDL 문법이 정확하다면 dba_objects 뷰의 객체 상태(status 컬럼)의 값은 VALID이기 때문에 Invalid Object로 확인되지 않고, 실행 시점에 ORA-00980 오류를 발생합니다.
따라서 시노님이 가리키는 1) 원본 객체의 소유자(User)가 삭제되었거나 2) 원본 객체(Table/View 등)가 삭제된 경우를 점검하는 스크립트가 필요합니다.

SET LINESIZE 300
SET PAGESIZE 1000
COL OWNER FOR A15
COL SYNONYM_NAME FOR A25
COL TABLE_OWNER FOR A15
COL TABLE_NAME FOR A25
COL ERROR_TYPE FOR A30
COL DROP_COMMAND FOR A100

SELECT 
    s.owner, 
    s.synonym_name, 
    s.table_owner, 
    s.table_name,
    CASE 
        WHEN u.username IS NULL THEN 'MISSING OWNER (USER)'
        WHEN o.object_name IS NULL THEN 'MISSING OBJECT (TABLE/VIEW)'
        ELSE 'INVALID STATUS'
    END AS ERROR_TYPE,
    'DROP ' || DECODE(s.owner, 'PUBLIC', 'PUBLIC SYNONYM ', 'SYNONYM ' || s.owner || '.') || s.synonym_name || ';' AS DROP_COMMAND
FROM dba_synonyms s
LEFT JOIN dba_users u   ON s.table_owner = u.username
LEFT JOIN dba_objects o ON s.table_owner = o.owner 
                       AND s.table_name = o.object_name
WHERE (u.username IS NULL OR o.object_name IS NULL)
  AND s.owner NOT IN ('SYS', 'SYSTEM') -- 시스템 기본 시노님 제외 옵션
ORDER BY s.owner, s.synonym_name;

이 스크립트를 통해 조회된 리스트가 생각보다 많을 수 있습니다. 특히 공용 시노님(Public Synonym)은 관리가 소홀하면 유령 데이터가 많이 남습니다. 삭제 대신 새로운 사용자(NEW_USER)로 일괄 변경(Replace)하려면 아래 스니핏을 위 쿼리에 추가합니다.

'CREATE OR REPLACE ' || DECODE(s.owner, 'PUBLIC', 'PUBLIC SYNONYM ', 'SYNONYM ' || s.owner || '.') || s.synonym_name || ' FOR NEW_USER.' || s.table_name || ';' AS FIX_COMMAND