[Oracle] CREATE SYNONYM statement - Oracle Public Dependency

SYNONYM

동의어가 순환 고리 유형으로 정의 오류는 크게 2가지 경우로 발생합니다.

첫 째, 테이블과 같은 객체 없이 동의어만 존재한 경우입니다.

이 부분은 또 2가지로 나뉠 수가 있는데, 1. 처음부터 스크립트의 문제로 테이블은 생성이 안 되고 동의어만 있을 때 나중에 조회를 시도해보는 경우, 2. 테이블과 동의어가 최초에는 정상적으로 실행이 되었으나 추후 cascade 형식이 아닌 테이블 하나만을 drop한 경우가 있습니다.

둘 째, 동일한 테이블 명이 각각 다른 계정에 있을 때 동의어를 기대한 사용자와 다르게 설정해 놓은 경우입니다.

테이블(table)에 동의어(synonym)가 설정된 상태에서 테이블이 삭제된 경우

CREATE PUBLIC SYNONYM SYNONYM_NAME FOR USER_NAME.OBJECT_NAME;

DROP TABLE TABLE_NAME;

위와 같이 SYNONYM 이 생성된 테이블을 DROP 이후 SELECT 하는 경우 ORA-01775 오류가 발생하게 됩니다. 이때, 테이블 생성, 혹은 동의어를 정리(혹은 권한관리)하면 됩니다. 동의어(시노님) 생성 문법은 다음과 같습니다.

CREATE [OR REPLACE] [PUBLIC] SYNONYM synonym_name FOR [schema_name.]object_name;

패키지(package)에서 사용하는 테이블이 drop 된 경우

패키지(package) 내에서 사용하는 테이블이 삭제된 경우에도 ORA-01775 예외가 발생할 수 있습니다. 이 경우 또한 'ORA-01775 동의어가 순환 고리 유형으로 정의되어 있습니다' 며 실행이 불가합니다. 상기의 예시와 비슷한 유형입니다. 패키지 내에서 사용하는, 동의어가 존재하는 테이블을 삭제한 것이 문제가 될 수 있습니다.

오라클 객체 의존성 확인하기 예시는 다음과 같습니다. OBJECT_NAME 하나만 조건을 추가했는데 상세히 기술하시면 됩니다. PUBLIC_DEPENDENCY 테이블과 DBA_OBJECTS 테이블을 이용합니다.

SELECT *
  FROM DBA_OBJECTS
 WHERE OBJECT_ID IN
          (    SELECT OBJECT_ID
                 FROM PUBLIC_DEPENDENCY
           CONNECT BY PRIOR OBJECT_ID = REFERENCED_OBJECT_ID
           START WITH REFERENCED_OBJECT_ID IN
                         (SELECT OBJECT_ID
                            FROM DBA_OBJECTS
                           WHERE 0 = 0 AND OBJECT_NAME = :OBJECT_NAME));

동의어 명령어

동의어를 생성하고 삭제하는 명령어에 대해 알아보도록 하겠습니다.

CREATE PUBLIC SYNONYM TABLE_NAME FOR USER_NAME.TABLE_NAME; --공개 동의어 생성
DROP PUBLIC SYNONYM TABLE_NAME; --공개 동의어 삭제
CREATE SYNONYM TABLE_NAME FOR USER_NAME.TABLE_NAME; --비공개 동의어 생성
DROP SYNONYM TABLE_NAME; --비공개 동의어 삭제
CREATE [OR REPLACE] [PUBLIC] SYNONYM SYNONYM_NAME FOR [USER_NAME.]OBJECT_NAME; --동의어 생성 문법
DROP [PUBLIC] SYNONYM SYNONYM_NAME; --동의어 삭제 문법

ALL_SYNONYMS 뷰는 동의어 이름, 어떠한 테이블에 설정이 되어 있는지, 디비링크 여부 등을 확인할 수 있는 뷰입니다.

SELECT *
  FROM ALL_SYNONYMS
 WHERE SYNONYM_NAME = 'TABLE_NAME';

OWNER                                                        SYNONYM_NAME                                                TABLE_OWNER                                                   TABLE_NAME                                                  DB_LINK
------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
PUBLIC                                                       TABLE_NAME                                                  USER_NAME                                                     TABLE_NAME

ALL_OBJECTS 오브젝트를 확인할 수 있는 데이터 딕셔너리 뷰에서 동의어 객체에 대한 정보를 조회해 보겠습니다. 소유자, 이름, 그리고 언제 생성이 되고 마지막으로 수정된 시간은 언제인지 자세하게 확인할 수 있습니다.

SELECT *
  FROM ALL_OBJECTS
 WHERE OBJECT_TYPE = 'SYNONYM' AND OBJECT_NAME = 'TABLE_NAME';

OWNER                                                        OBJECT_NAME
 SUBOBJECT_NAME                                         OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE                           CREATED  LAST_DDL TIMESTAMP                               STATUS         TE GE SE  NAMESPACE EDITION_NAME
------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ ---------- -------------- -------------------------------------- -------- -------- -------------------------------------- -------------- -- -- -- ---------- ------------------------------------------------------------
PUBLIC                                                       TABLE_NAME
   0                 SYNONYM                                  
VALID           N  N  N           1