Oracle ORA-00036 오류 원인과 해결 방법 완벽 가이드

ORA-00036
2026년 05월 29일 | DBMS Error 가이드

이 글에서 다루는 내용

ORA-00036 에러의 원인 분석, 해결 SQL, 예방 방법을 실무 관점에서 정리합니다.

ORA-00036 maximum number of recursive SQL levels exceeded 는?

ORA-00036 에러는 Oracle 데이터베이스에서 재귀적 SQL 호출이 허용된 최대 레벨을 초과했을 때 발생합니다. Oracle 내부적으로 재귀 SQL의 최대 깊이는 기본적으로 2000레벨로 제한되어 있으며, 이를 초과하는 경우 해당 에러가 발생합니다. 주로 트리거(Trigger), 저장 프로시저(Stored Procedure), 또는 뷰(View)가 서로를 무한히 호출하거나 자기 자신을 반복 호출하는 순환 참조(Circular Reference) 구조에서 자주 목격됩니다.

주요 발생 원인

트리거의 상호 순환 호출 (Mutating Trigger / Circular Trigger)

가장 흔한 원인입니다. 테이블 A의 트리거가 테이블 B를 DML로 변경하고, 테이블 B의 트리거가 다시 테이블 A를 변경하는 구조에서 발생합니다. 이 경우 트리거가 서로를 무한히 호출하면서 재귀 레벨이 급격히 증가하여 ORA-00036을 유발합니다. 실무에서는 복잡한 감사(Audit) 로직이나 자동화된 데이터 동기화 로직을 구현할 때 자주 발생합니다.

저장 프로시저 또는 함수의 무한 재귀 호출

PL/SQL로 작성된 프로시저나 함수가 종료 조건(Base Case) 없이 자기 자신을 재귀 호출하거나, 두 함수가 서로를 반복 호출하는 경우입니다. 재귀 알고리즘을 구현할 때 종료 조건을 잘못 설정하거나 아예 누락한 경우에 발생합니다. 특히 트리 구조 데이터나 계층형 데이터를 처리하는 재귀 로직에서 빈번하게 나타납니다.

뷰(View)의 순환 참조

뷰가 자기 자신이나 다른 뷰를 참조하고, 그 뷰가 다시 원래 뷰를 참조하는 순환 구조를 가질 때 발생합니다. 복잡한 비즈니스 로직을 여러 겹의 뷰로 표현할 때 실수로 순환 참조가 생길 수 있습니다. 뷰가 중첩될수록 재귀 레벨이 깊어지므로 DBA가 뷰 의존성을 정기적으로 점검해야 합니다.

해결 방법

원인 1: 트리거 순환 참조 해결

먼저 어떤 트리거들이 서로 연관되어 있는지 확인합니다.

-- 특정 테이블에 걸린 트리거 목록 조회
SELECT TRIGGER_NAME, TRIGGER_TYPE, TRIGGERING_EVENT, TABLE_NAME, STATUS
FROM USER_TRIGGERS
WHERE TABLE_NAME IN ('TABLE_A', 'TABLE_B')
ORDER BY TABLE_NAME;

순환을 유발하는 트리거를 임시로 비활성화하여 원인을 격리합니다.

-- 문제 트리거 비활성화
ALTER TRIGGER trg_table_a_after_update DISABLE;
ALTER TRIGGER trg_table_b_after_update DISABLE;

패키지 수준의 전역 플래그를 사용하여 트리거의 재진입(Re-entry)을 방지하는 방식으로 수정합니다.

-- 재진입 방지를 위한 패키지 생성
CREATE OR REPLACE PACKAGE pkg_trigger_control AS
    g_trigger_running BOOLEAN := FALSE;
END pkg_trigger_control;
/

-- 트리거에 재진입 방지 로직 추가
CREATE OR REPLACE TRIGGER trg_table_a_after_update
AFTER UPDATE ON table_a
FOR EACH ROW
BEGIN
    IF NOT pkg_trigger_control.g_trigger_running THEN
        pkg_trigger_control.g_trigger_running := TRUE;
        BEGIN
            -- 실제 비즈니스 로직 수행
            UPDATE table_b SET col1 = :NEW.col1 WHERE id = :NEW.id;
            pkg_trigger_control.g_trigger_running := FALSE;
        EXCEPTION
            WHEN OTHERS THEN
                pkg_trigger_control.g_trigger_running := FALSE;
                RAISE;
        END;
    END IF;
END;
/

원인 2: 무한 재귀 프로시저/함수 수정

재귀 함수에 반드시 종료 조건(Base Case)과 최대 재귀 깊이 제한을 추가합니다.

-- 잘못된 재귀 함수 예시 (종료 조건 없음 - 절대 사용 금지)
CREATE OR REPLACE FUNCTION bad_recursive_func(p_val IN NUMBER)
RETURN NUMBER IS
BEGIN
    -- 종료 조건 없음: ORA-00036 발생
    RETURN bad_recursive_func(p_val + 1);
END;
/

-- 올바른 재귀 함수 예시 (종료 조건 + 깊이 제한 포함)
CREATE OR REPLACE FUNCTION good_recursive_func(
    p_val     IN NUMBER,
    p_depth   IN NUMBER DEFAULT 0
) RETURN NUMBER IS
    c_max_depth CONSTANT NUMBER := 100; -- 최대 재귀 깊이 제한
BEGIN
    -- 종료 조건 1: 최대 깊이 초과 방지
    IF p_depth > c_max_depth THEN
        RAISE_APPLICATION_ERROR(-20001, '최대 재귀 깊이를 초과했습니다: ' || p_depth);
    END IF;

    -- 종료 조건 2: 비즈니스 로직 상의 종료 조건
    IF p_val <= 0 THEN
        RETURN 0;
    END IF;

    RETURN p_val + good_recursive_func(p_val - 1, p_depth + 1);
END;
/

계층형 데이터는 재귀 함수 대신 Oracle의 CONNECT BY 또는 WITH 절을 활용하는 것이 훨씬 안전합니다.

-- 재귀 PL/SQL 대신 계층형 쿼리 사용 권장
-- CONNECT BY 활용
SELECT LEVEL, employee_id, manager_id, last_name
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id;

-- WITH절(CTE) 재귀 쿼리 활용
WITH emp_hierarchy (employee_id, manager_id, last_name, depth) AS (
    -- Anchor Member
    SELECT employee_id, manager_id, last_name, 0 AS depth
    FROM employees
    WHERE manager_id IS NULL
    UNION ALL
    -- Recursive Member
    SELECT e.employee_id, e.manager_id, e.last_name, eh.depth + 1
    FROM employees e
    JOIN emp_hierarchy eh ON e.manager_id = eh.employee_id
    WHERE eh.depth < 50  -- 최대 깊이 제한
)
SELECT * FROM emp_hierarchy ORDER BY depth, employee_id;

원인 3: 뷰 순환 참조 진단 및 해결

뷰 간의 의존성을 조회하여 순환 참조 여부를 파악합니다.

-- 뷰 의존성 조회 (DBA 권한 필요)
SELECT name, referenced_name, referenced_type
FROM dba_dependencies
WHERE type = 'VIEW'
  AND referenced_type = 'VIEW'
ORDER BY name;

-- 특정 뷰의 정의 확인
SELECT VIEW_NAME, TEXT
FROM USER_VIEWS
WHERE VIEW_NAME IN ('VIEW_A', 'VIEW_B');

순환 참조가 발견되면 뷰 구조를 재설계하여 직접 테이블을 참조하도록 변경합니다.

-- 순환 참조 제거: 뷰 대신 실제 테이블 직접 참조
CREATE OR REPLACE VIEW view_a AS
SELECT t.col1, t.col2, t.col3
FROM base_table t  -- view_b 대신 base_table 직접 참조
WHERE t.status = 'ACTIVE';

예방 방법

코드 리뷰 및 트리거/프로시저 의존성 문서화

신규 트리거, 프로시저, 뷰를 배포하기 전에 반드시 코드 리뷰를 통해 순환 참조 가능성을 사전 점검해야 합니다. DBA_DEPENDENCIES 또는 ALL_DEPENDENCIES 뷰를 활용하여 객체 간 의존성 맵을 주기적으로 생성하고 문서화하면 복잡한 시스템에서도 순환 구조를 조기에 발견할 수 있습니다.

“`sql

— 전체 PL/SQL 객체 의존성 현황 조회 (주기적 모니터링용)

SELECT owner, name, type, referenced_owner, referenced_name, referenced_type

FROM dba_dependencies

WHERE type IN (‘PROCEDURE’, ‘FUNCTION’, ‘TRIGGER’, ‘PACKAGE’, ‘VIEW’)

AND owner = ‘YOUR_SCHEMA’

ORDER BY name, referenced_name;

“`

재귀 로직에 반드시 깊이 제한 및 종료 조건 강제화

모든 재귀 PL/SQL 로직을 작성할 때 코딩 표준(Coding Standard)으로 최대 재귀 깊이 파라미터와 명시적 종료 조건을 의무화해야 합니다. 개발 환경에서는 단위 테스트(Unit Test)를 통해 재귀 종료 조건이 정상 동작하는지 반드시 검증하고, 스테이징 환경에서도 경계값 테스트(Boundary Test)를 수행하여 운영 배포 전에 문제를 사전 차단하는 것이 Best Practice입니다.