DEV Community: umzzil nng

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

umzzil nng — Fri, 22 May 2026 14:01:14 +0000

ORA-00064: object is too large to allocate on this OS

30년 경력 Oracle DBA의 실무 경험을 바탕으로 작성한 트러블슈팅 가이드입니다.

ORA-00064란?

ORA-00064는 Oracle 인스턴스 기동 시 또는 특정 메모리 할당 요청 시, 운영체제(OS)가 해당 객체(주로 SGA 또는 공유 메모리 세그먼트)를 위한 메모리를 할당할 수 없을 때 발생하는 에러입니다. 즉, Oracle이 init.ora 또는 spfile에 설정된 파라미터 값(예: sga_target, processes, sga_max_size 등)에 따라 메모리 자원을 요청했지만, OS 레벨의 커널 파라미터 또는 물리적 메모리 한계로 인해 할당이 거부된 상황입니다. 이 에러는 특히 새로운 Oracle 인스턴스를 처음 기동하거나, 파라미터 변경 후 재기동 시 자주 접하게 되며, DBA라면 반드시 해결 방법을 숙지해야 합니다.

주요 발생 원인

1. OS 커널의 공유 메모리 파라미터(SHMMAX/SHMALL) 설정 부족

가장 빈번한 원인입니다. Linux/Unix 환경에서 Oracle SGA는 공유 메모리(Shared Memory Segment)를 사용하는데, OS 커널 파라미터인 SHMMAX(단일 공유 메모리 세그먼트 최대 크기)나 SHMALL(전체 공유 메모리 페이지 합계)이 Oracle이 요청하는 SGA 크기보다 작게 설정되어 있을 경우 이 에러가 발생합니다. 예를 들어 sga_target=8G로 설정했는데 SHMMAX가 2GB로 제한되어 있다면, OS는 요청을 거부합니다.

2. Oracle 파라미터 값이 물리 메모리(RAM)를 초과하거나 과도하게 설정됨

sga_target, sga_max_size, memory_target, pga_aggregate_target 등의 파라미터 합산 값이 실제 서버에 장착된 물리 메모리를 초과하도록 잘못 설정된 경우에도 이 에러가 발생합니다. 특히 memory_target 또는 memory_max_target을 사용하는 AMM(Automatic Memory Management) 구성에서, /dev/shm(tmpfs) 크기가 memory_target 값보다 작은 경우 Linux 환경에서 이 에러가 빈번하게 발생합니다.

3. processes 파라미터 과도 설정으로 인한 OS 자원 한계 초과

processes 파라미터는 Oracle 인스턴스에 동시에 접속할 수 있는 최대 프로세스 수를 정의하며, 이 값이 매우 크게 설정된 경우 내부적으로 필요한 공유 메모리 구조체(예: 래치, 잠금 구조 등)의 크기도 비례하여 증가합니다. OS의 최대 프로세스 수 제한(kernel.pid_max, ulimit 등)을 초과하거나, 해당 구조체를 위한 메모리 할당이 OS 한계를 넘어설 때 ORA-00064가 발생할 수 있습니다.

해결 방법

원인 1 해결: OS 커널 공유 메모리 파라미터 조정 (Linux 기준)

현재 커널 파라미터 값을 확인하고 Oracle SGA 크기에 맞게 조정합니다.

-- 현재 SGA 관련 파라미터 확인 (Oracle 기동 가능한 경우)
SELECT name, value
FROM v$parameter
WHERE name IN ('sga_target', 'sga_max_size', 'memory_target', 'memory_max_target')
ORDER BY name;

# OS에서 현재 공유 메모리 커널 파라미터 확인
$ ipcs -lm
$ sysctl -a | grep -i shm

# /etc/sysctl.conf 수정 예시 (SGA = 8GB 기준)
# SHMMAX: 단일 세그먼트 최대 크기 (bytes) - SGA보다 크게 설정
# SHMALL: 전체 공유 메모리 총 페이지 수 (page size = 4096 bytes 기준)

$ vi /etc/sysctl.conf

# 아래 값을 추가 또는 수정 (8GB SGA 기준)
kernel.shmmax = 10737418240    # 10GB (bytes)
kernel.shmall = 2621440        # 10GB / 4096 = 2621440 pages
kernel.shmmni = 4096

# 변경사항 즉시 적용
$ sysctl -p

-- spfile에서 SGA 파라미터 조정 (필요 시 줄이기)
-- sqlplus / as sysdba 접속 후 수행

ALTER SYSTEM SET sga_target = 6G SCOPE=SPFILE;
ALTER SYSTEM SET sga_max_size = 6G SCOPE=SPFILE;

-- 변경 후 인스턴스 재기동
SHUTDOWN IMMEDIATE;
STARTUP;

원인 2 해결: AMM 환경에서 /dev/shm 크기 조정 및 파라미터 수정

# /dev/shm 현재 크기 확인
$ df -h /dev/shm

# /dev/shm 크기를 memory_target 이상으로 조정 (예: 12GB)
$ mount -o remount,size=12G /dev/shm

# 영구 적용을 위해 /etc/fstab 수정
$ vi /etc/fstab
# 아래 라인 추가 또는 수정
tmpfs  /dev/shm  tmpfs  defaults,size=12G  0 0

-- AMM 비활성화 후 ASMM으로 전환 (권장)
-- AMM(memory_target) 대신 ASMM(sga_target + pga_aggregate_target) 사용

-- pfile 생성 후 직접 편집
CREATE PFILE='/tmp/init_fix.ora' FROM SPFILE;
-- 에디터로 /tmp/init_fix.ora 열어 아래와 같이 수정
-- memory_target=0
-- memory_max_target=0
-- sga_target=6G
-- pga_aggregate_target=2G

-- 수정된 pfile로 기동
STARTUP PFILE='/tmp/init_fix.ora';

-- 정상 기동 확인 후 spfile 재생성
CREATE SPFILE FROM PFILE='/tmp/init_fix.ora';

-- AMM 완전 비활성화 확인
SELECT name, value
FROM v$parameter
WHERE name IN ('memory_target','memory_max_target','sga_target','pga_aggregate_target');

원인 3 해결: processes 파라미터 적정값 조정

-- 현재 processes 설정 및 실제 사용 현황 확인
SELECT name, value
FROM v$parameter
WHERE name = 'processes';

-- 현재 실제 접속 프로세스 수 확인
SELECT COUNT(*) AS current_processes
FROM v$process;

-- 최대 동시 세션 이력 확인 (AWR 사용 가능 시)
SELECT snap_id,
       instance_number,
       num_lcpus,
       MAX(num_cpus) AS max_cpus
FROM dba_hist_osstat
GROUP BY snap_id, instance_number, num_lcpus;

-- 적정 수준으로 processes 파라미터 조정 예시
-- 일반적으로 (최대 동시 세션 수 * 1.2) + 여유분 으로 설정
ALTER SYSTEM SET processes = 500 SCOPE=SPFILE;

-- OS ulimit 확인 및 조정 (oracle 계정 기준)
-- /etc/security/limits.conf 설정 예시

# oracle 계정 ulimit 확인
$ su - oracle
$ ulimit -a | grep -E "open files|max user processes"

# /etc/security/limits.conf 수정
$ vi /etc/security/limits.conf

oracle  soft  nproc   16384
oracle  hard  nproc   16384
oracle  soft  nofile  65536
oracle  hard  nofile  65536

# 적용 확인 (재로그인 후)
$ ulimit -u

예방 방법

1. Oracle 설치 전/후 OS 커널 파라미터 사전 검증 자동화

Oracle 공식 권고안(Oracle Database Installation Guide)에 명시된 커널 파라미터 최솟값을 기반으로, 인스턴스 기동 전 사전 검증 스크립트를 작성하여 운영 표준으로 채택하는 것이 좋습니다. 아래 스크립트를 cron 또는 Ansible playbook에 통합하면, 파라미터 변경 후 재기동 시 발생할 수 있는 장애를 사전에 차단할 수 있습니다.

#!/bin/bash
# Oracle SGA/커널 파라미터 사전 검증 스크립트

SGA_TARGET_GB=8  # 설정 예정 SGA (GB)
SGA_BYTES=$((SGA_TARGET_GB * 1024 * 1024 * 1024))
PAGE_SIZE=$(getconf PAGE_SIZE)

SHMMAX=$(sysctl -n kernel.shmmax)
SHMALL=$(sysctl -n kernel.shmall)
SHMALL_BYTES=$((SHMALL * PAGE_SIZE))
SHM_DEV=$(df /dev/shm | awk 'NR==2{print $2}')  # KB 단위

echo "=== Oracle 메모리 파라미터 검증 ==="
echo "목표 SGA 크기    : ${SGA_TARGET_GB}GB (${SGA_BYTES} bytes)"
echo "현재 SHMMAX     : ${SHMMAX} bytes"
echo "현재 SHMALL     : ${SHMALL_BYTES} bytes"
echo "/dev/shm 크기   : $((SHM_DEV / 1024 / 1024))GB"

if [ "$SHMMAX" -lt "$SGA_BYTES" ]; then
    echo "[WARNING] SHMMAX가 SGA 크기보다 작습니다. 조정 필요!"
else
    echo "[OK] SHMMAX 설정 정상"
fi

if [ "$SHMALL_BYTES" -lt "$SGA_BYTES" ]; then
    echo "[WARNING] SHMALL이 SGA 크기보다 작습니다. 조정 필요!"
else
    echo "[OK] SHMALL 설정 정상"
fi

-- Oracle 파라미터 변경 이력 관리 쿼리 (변경 전후 비교용)
SELECT name,
       value          AS current_value,
       default_value,
       description
FROM v$parameter
WHERE name IN (
    'sga_target', 'sga_max_size',
    'pga_aggregate_target',
    'memory_target', 'memory_max_target',
    'processes', 'sessions'
)
ORDER BY name;

2. 파라미터 변경 시 반드시 SCOPE=SPFILE 단계적 적용 및 변경 관리 프로세스 수립

운영 환경에서 메모리 관련 파라미터를 변경할 때는 반드시 pfile을 사전에 백업하고, SCOPE=SPFILE로 변경한 뒤 재기동 전에 OS 설정과의 정합성을 다시 한번 검토하는 절차를 변경 관리 프로세스로 문서화해야 합니다. 특히 클라우드(OCI, AWS RDS 제외 EC2 자체 설치 등) 또는 가상화 환경에서는 물리 메모리와 할당 가능 메모리 간의 차이가 존재할 수 있으므로, free -h, vmstat, ipcs -m 명령어를 통한 실시간 메모리 현황 모니터링을 재기동 전 필수 체크리스트로 포함시키는 것을 강력히 권장합니다.

-- 변경 전 현재 spfile 내용 백업
CREATE PFILE='/backup/oracle/pfile_backup_YYYYMMDD.ora' FROM SPFILE;

-- 변경 후 검증을 위한 파라미터 확인
SELECT name, value, ismodified, description
FROM v$parameter
WHERE ismodified != 'FALSE'
ORDER BY name;

에러 코드	설명
ORA-27102	`out of memory` - OS가 요청된 메모리를 할당할 수 없을 때 발생. ORA-00064와 함께 alert log에 연달아 나타나는 경우가 많음
ORA-27123	`unable to attach to shared memory segment` - 공유 메모리 세그먼트에 연결 자체가 불가능할 때 발생. SHM

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

umzzil nng — Fri, 22 May 2026 10:01:10 +0000

ORA-00060란?

ORA-00060은 Oracle 데이터베이스에서 두 개 이상의 세션이 서로 상대방이 보유한 리소스(주로 테이블 행 잠금)를 기다리는 교착 상태(Deadlock)가 발생했을 때 나타나는 에러입니다. 예를 들어 세션 A가 행1을 잠그고 행2를 기다리는 동안, 세션 B는 행2를 잠근 채 행1을 기다리는 상황이 전형적인 사례입니다. Oracle은 이 상태를 자동으로 감지하여 두 세션 중 하나를 희생자(Victim)로 선택하고 해당 트랜잭션을 롤백하면서 이 에러를 발생시킵니다. 중요한 점은 Oracle이 전체 트랜잭션이 아닌 마지막 DML 문장 하나만 롤백한다는 사실이며, 따라서 애플리케이션 레벨에서 반드시 에러를 처리하고 적절히 대응해야 합니다.

주요 발생 원인

1. 서로 다른 순서로 동일한 리소스에 접근하는 트랜잭션

가장 빈번한 원인으로, 여러 트랜잭션이 동일한 테이블의 행들을 서로 다른 순서로 잠금(Lock)을 획득할 때 발생합니다. 예를 들어 주문 처리 로직에서 하나의 프로세스는 ORDER → ORDER_ITEM 순서로, 다른 프로세스는 ORDER_ITEM → ORDER 순서로 업데이트를 시도하면 교착 상태가 즉시 발생합니다. 이는 애플리케이션 설계 단계에서 잠금 획득 순서를 통일하지 않은 경우에 빈번히 나타납니다.

2. 인덱스 부재로 인한 불필요한 전체 테이블 잠금

외래 키(Foreign Key) 컬럼에 인덱스가 없을 경우, 부모 테이블의 행을 업데이트하거나 삭제할 때 Oracle은 자식 테이블 전체에 테이블 레벨 잠금(Table-Level Lock)을 걸게 됩니다. 이 상황에서 다른 세션이 자식 테이블에 DML을 시도하면 잠금 충돌이 발생하고 교착 상태로 이어집니다. 특히 대용량 OLTP 환경에서 이 원인은 성능 저하와 함께 데드락을 폭발적으로 증가시키는 주범입니다.

3. 비트맵 인덱스(Bitmap Index)가 있는 테이블에 대한 동시 DML

비트맵 인덱스는 하나의 인덱스 엔트리가 여러 행의 정보를 담기 때문에, 한 행을 업데이트하면 동일한 비트맵 세그먼트를 공유하는 다른 행들도 잠금 범위에 포함됩니다. 여러 세션이 동시에 비트맵 인덱스가 설정된 컬럼의 값을 업데이트하면 잠금 범위가 겹치면서 교착 상태가 매우 쉽게 발생합니다. 이 원인은 DW(Data Warehouse) 환경에서 데이터 로딩 시 종종 나타납니다.

해결 방법

원인 1 해결: 트랜잭션 잠금 순서 통일

데드락이 발생한 세션을 먼저 V$LOCKED_OBJECT와 트레이스 파일로 확인합니다.

-- 현재 잠금 상황 및 블로킹 세션 확인
SELECT
    l.session_id,
    s.serial#,
    s.username,
    s.program,
    l.oracle_username,
    l.os_user_name,
    o.object_name,
    o.object_type,
    DECODE(l.locked_mode,
        0, 'None',
        1, 'Null',
        2, 'Row-S (SS)',
        3, 'Row-X (SX)',
        4, 'Share',
        5, 'S/Row-X (SSX)',
        6, 'Exclusive', 'Unknown') AS lock_mode
FROM
    v$locked_object  l
    JOIN dba_objects  o ON l.object_id = o.object_id
    JOIN v$session    s ON l.session_id = s.sid
ORDER BY
    l.session_id;

-- 데드락 트레이스 파일 위치 확인
SELECT value FROM v$diag_info WHERE name = 'Default Trace File';

-- 혹은 알림 로그에서 데드락 발생 시각 확인
SELECT originating_timestamp, message_text
FROM   v$diag_alert_ext
WHERE  message_text LIKE '%deadlock%'
ORDER BY originating_timestamp DESC
FETCH FIRST 20 ROWS ONLY;

잠금 순서를 통일하는 실무 패턴 예시입니다. 반드시 작은 PRIMARY KEY 순서로 정렬하여 업데이트합니다.

-- 나쁜 예: 순서가 보장되지 않아 데드락 발생 가능
-- 세션 A
UPDATE orders SET status = 'PROCESSING' WHERE order_id = 1001;
UPDATE order_items SET qty = qty - 1 WHERE item_id = 5001;

-- 세션 B (역순 접근 → 데드락 위험)
UPDATE order_items SET qty = qty - 1 WHERE item_id = 5001;
UPDATE orders SET status = 'PROCESSING' WHERE order_id = 1001;

-- 좋은 예: 항상 동일한 순서(orders → order_items)로 접근
-- 세션 A
BEGIN
    -- 1단계: 항상 부모 테이블 먼저
    UPDATE orders
    SET    status = 'PROCESSING'
    WHERE  order_id = 1001;

    -- 2단계: 자식 테이블은 항상 나중에, PK 오름차순 정렬
    UPDATE order_items
    SET    qty = qty - 1
    WHERE  order_id = 1001
    ORDER BY item_id;  -- 순서 보장

    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE;
END;
/

원인 2 해결: 외래 키 인덱스 생성

-- 외래 키가 있으나 인덱스가 없는 컬럼 찾기 (실무 필수 쿼리)
SELECT
    c.table_name,
    c.constraint_name,
    c.column_name,
    c.position
FROM
    user_cons_columns c
    JOIN user_constraints uc
        ON c.constraint_name = uc.constraint_name
        AND uc.constraint_type = 'R'  -- R = Foreign Key
WHERE
    NOT EXISTS (
        SELECT 1
        FROM   user_ind_columns ic
        WHERE  ic.table_name  = c.table_name
          AND  ic.column_name = c.column_name
    )
ORDER BY
    c.table_name, c.position;

-- 발견된 FK 컬럼에 인덱스 생성
-- 예시: ORDER_ITEMS 테이블의 ORDER_ID(FK) 컬럼
CREATE INDEX idx_order_items_order_id
    ON order_items (order_id)
    TABLESPACE users
    PARALLEL 4
    NOLOGGING;

-- 인덱스 생성 후 통계 수집
BEGIN
    DBMS_STATS.GATHER_TABLE_STATS(
        ownname          => USER,
        tabname          => 'ORDER_ITEMS',
        estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
        cascade          => TRUE
    );
END;
/

원인 3 해결: 비트맵 인덱스 환경에서의 동시성 제어

-- OLTP 환경이라면 비트맵 인덱스를 B-Tree 인덱스로 교체
-- 기존 비트맵 인덱스 확인
SELECT index_name, index_type, table_name
FROM   user_indexes
WHERE  index_type = 'BITMAP';

-- 비트맵 인덱스 제거 후 B-Tree 인덱스로 대체
DROP INDEX bitmap_idx_status;

CREATE INDEX btree_idx_status
    ON orders (status)
    TABLESPACE users;

-- 대용량 배치 로딩 시 비트맵 인덱스가 필요한 경우:
-- 로딩 전 인덱스 비활성화 → 로딩 → 인덱스 재생성 패턴 사용
ALTER INDEX bitmap_idx_status UNUSABLE;

-- 데이터 로딩 수행
INSERT /*+ APPEND */ INTO orders SELECT * FROM orders_stg;
COMMIT;

-- 인덱스 재생성
ALTER INDEX bitmap_idx_status REBUILD PARALLEL 8 NOLOGGING;
ALTER INDEX bitmap_idx_status NOPARALLEL;
ALTER INDEX bitmap_idx_status LOGGING;

데드락 발생 시 애플리케이션 재시도 로직 (PL/SQL)

-- 실무에서 바로 활용 가능한 데드락 재시도 프로시저
CREATE OR REPLACE PROCEDURE process_order_with_retry(
    p_order_id  IN orders.order_id%TYPE,
    p_max_retry IN INTEGER DEFAULT 3
)
AS
    v_retry_count INTEGER := 0;
    deadlock_detected EXCEPTION;
    PRAGMA EXCEPTION_INIT(deadlock_detected, -60);  -- ORA-00060
BEGIN
    LOOP
        BEGIN
            -- 실제 비즈니스 로직
            UPDATE orders
            SET    status     = 'PROCESSING',
                   updated_dt = SYSDATE
            WHERE  order_id   = p_order_id;

            UPDATE order_items
            SET    status = 'PROCESSING'
            WHERE  order_id = p_order_id;

            COMMIT;
            EXIT;  -- 성공 시 루프 탈출

        EXCEPTION
            WHEN deadlock_detected THEN
                ROLLBACK;
                v_retry_count := v_retry_count + 1;

                IF v_retry_count >= p_max_retry THEN
                    -- 최대 재시도 횟수 초과 시 로그 기록 후 예외 전파
                    INSERT INTO error_log (
                        error_code, error_msg,
                        session_id, occurred_dt
                    ) VALUES (
                        -60, 'Deadlock exceeded max retry: ' || p_order_id,
                        SYS_CONTEXT('USERENV','SID'), SYSTIMESTAMP
                    );
                    COMMIT;
                    RAISE;
                END IF;

                -- 잠시 대기 후 재시도 (지수 백오프 적용)
                DBMS_LOCK.SLEEP(v_retry_count * 0.5);
        END;
    END LOOP;
END process_order_with_retry;
/

예방 방법

1. 일관된 오브젝트 접근 순서 표준화 및 코드 리뷰 의무화

모든 개발자가 동일한 테이블 접근 순서를 따르도록 개발 표준 문서에 명시해야 합니다. 구체적으로는 "항상 부모 테이블 → 자식 테이블 순서로 DML을 수행하고, 동일 테이블 내 다중 행 업데이트 시에는 반드시 PK 오름차순으로 정렬한다"는 규칙을 정립합니다. 코드 리뷰 단계에서 이 순서를 위반하는 트랜잭션을 사전에 차단하고, CI/CD 파이프라인에 정적 분석 도구를 통합하여 자동 검사하는 것이 이상적입니다. 또한 신규 기능 개발 시 데이터 모델 리뷰 단계에서 DBA가 잠금 경합 가능성을 검토하는 게이팅 프로세스를 도입하면 운영 환경에서의 데드락 발생을 크게 줄일 수 있습니다.

2. 주기적인 FK 인덱스 점검 및 AWR/트레이스 모니터링 자동화

데이터베이스 운영 중 새로운 테이블과 FK 제약조건이 추가될 때마다 인덱스 누락 여부를 자동으로 점검하는 스크립트를 스케줄러(DBMS_SCHEDULER)에 등록해 두어야 합니다. AWR(Automatic Workload Repository) 리포트에서 enq: TX - row lock contention 대기 이벤트가 급증하는 경우 데드락 전조일 수 있으므로, 임계값 초과 시 알림을 발송하는 모니터링 체계를 구축하는 것이 중요합니다. Oracle Alert Log와 트레이스 파일을 자동으로 파싱하여 ORA-00060 발생 빈도를 추적하고, 발생 추세가 증가할 때 즉시 분석에 착수할 수 있는 운영

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

umzzil nng — Fri, 22 May 2026 06:01:04 +0000

ORA-00057란?

ORA-00057은 Oracle 데이터베이스에서 임시 테이블(Temporary Table)에 대한 잠금(Lock) 수가 시스템에서 허용하는 최대 임계치를 초과했을 때 발생하는 에러입니다. 이 에러는 주로 다수의 세션이 동시에 전역 임시 테이블(Global Temporary Table, GTT)을 집중적으로 사용하거나, 단일 세션이 과도하게 많은 임시 테이블 잠금을 보유하고 있을 때 나타납니다. 운영 환경에서 갑작스럽게 발생하면 트랜잭션 처리가 중단되고 애플리케이션 장애로 이어질 수 있으므로 신속한 원인 파악과 대응이 필수적입니다.

주요 발생 원인

1. 과도한 동시 세션의 전역 임시 테이블 사용

Oracle의 전역 임시 테이블(GTT)은 세션 혹은 트랜잭션 단위로 데이터를 격리하여 저장하는 구조입니다. 수백 개 이상의 세션이 동시에 동일한 GTT에 접근하거나, 각 세션이 여러 GTT를 동시에 사용할 경우 내부적으로 관리되는 임시 테이블 잠금 카운터가 한계에 도달하여 ORA-00057이 발생합니다. 특히 커넥션 풀(Connection Pool) 환경에서 세션 수가 급증하는 배치 처리 시간대나 트래픽 피크 타임에 집중적으로 나타나는 패턴을 보입니다.

2. 트랜잭션 미종료로 인한 잠금 누적

애플리케이션 코드에서 COMMIT 또는 ROLLBACK 없이 트랜잭션이 장시간 열린 상태로 유지되면, 해당 세션이 보유한 임시 테이블 잠금이 해제되지 않고 계속 누적됩니다. 특히 예외 처리 로직이 미흡한 애플리케이션에서 오류 발생 후 세션이 좀비 상태(zombie session)로 남아있는 경우, 잠금이 무한정 유지되어 전체 시스템의 임시 테이블 잠금 가용 수를 모두 소진시키는 원인이 됩니다.

3. 잘못된 GTT 설계 및 남용

하나의 업무 로직 안에서 필요 이상으로 많은 전역 임시 테이블을 생성하거나, 영구 테이블(Permanent Table)로 처리해야 할 대용량 데이터를 무분별하게 GTT에 적재하는 설계 문제도 주요 원인입니다. GTT는 편리하지만 잠금 자원을 소비하는 객체이므로, 설계 단계에서 사용 수와 용도를 명확히 제한하지 않으면 운영 중 예기치 않게 ORA-00057 에러를 유발할 수 있습니다.

해결 방법

원인 1 해결 - 현재 임시 테이블 잠금 현황 파악 및 세션 정리

먼저 현재 어떤 세션이 얼마나 많은 임시 테이블 잠금을 보유하고 있는지 확인합니다.

-- 현재 임시 테이블 잠금을 보유한 세션 조회
SELECT
    s.sid,
    s.serial#,
    s.username,
    s.status,
    s.machine,
    s.program,
    s.logon_time,
    COUNT(l.id1) AS temp_lock_count
FROM
    v$session s
    JOIN v$lock l ON s.sid = l.sid
WHERE
    l.type = 'TT'  -- TT: Temporary Table Lock
GROUP BY
    s.sid, s.serial#, s.username, s.status,
    s.machine, s.program, s.logon_time
ORDER BY
    temp_lock_count DESC;

-- 전체 임시 테이블 잠금 수 확인
SELECT
    COUNT(*) AS total_temp_locks,
    COUNT(DISTINCT sid) AS affected_sessions
FROM
    v$lock
WHERE
    type = 'TT';

과도한 잠금을 보유한 특정 세션을 강제 종료해야 할 경우 아래 쿼리를 사용합니다.

-- 문제 세션 강제 종료 (DBA 권한 필요)
-- 먼저 SID와 SERIAL# 확인 후 실행
ALTER SYSTEM KILL SESSION '1234,5678' IMMEDIATE;

-- 여러 좀비 세션을 일괄 처리하는 스크립트 생성
SELECT
    'ALTER SYSTEM KILL SESSION ''' || sid || ',' || serial# || ''' IMMEDIATE;'
        AS kill_statement
FROM
    v$session
WHERE
    status = 'INACTIVE'
    AND last_call_et > 3600  -- 1시간 이상 비활성 세션
    AND username IS NOT NULL;

원인 2 해결 - 미종료 트랜잭션 탐지 및 정리

오랫동안 열려 있는 트랜잭션을 찾아내어 조치합니다.

-- 장시간 열린 트랜잭션 조회 (1시간 이상)
SELECT
    s.sid,
    s.serial#,
    s.username,
    s.machine,
    s.program,
    t.start_time,
    ROUND((SYSDATE - TO_DATE(t.start_time, 'MM/DD/YY HH24:MI:SS')) * 24, 2)
        AS elapsed_hours,
    t.used_ublk AS undo_blocks_used,
    t.log_io,
    t.phy_io
FROM
    v$transaction t
    JOIN v$session s ON t.addr = s.taddr
WHERE
    (SYSDATE - TO_DATE(t.start_time, 'MM/DD/YY HH24:MI:SS')) * 24 > 1
ORDER BY
    elapsed_hours DESC;

-- GTT 사용 현황과 트랜잭션 상태를 함께 조회
SELECT
    s.sid,
    s.serial#,
    s.username,
    s.status,
    NVL(t.start_time, 'NO ACTIVE TXN') AS txn_start_time,
    l.type AS lock_type,
    COUNT(l.id1) OVER (PARTITION BY s.sid) AS lock_count
FROM
    v$session s
    LEFT JOIN v$transaction t ON s.taddr = t.addr
    LEFT JOIN v$lock l ON s.sid = l.sid AND l.type = 'TT'
WHERE
    l.type IS NOT NULL
ORDER BY
    s.sid;

원인 3 해결 - GTT 사용 현황 감사 및 재설계

현재 데이터베이스에 존재하는 GTT 전체 목록과 사용 빈도를 확인합니다.

-- 현재 스키마의 전역 임시 테이블 목록 조회
SELECT
    owner,
    table_name,
    duration,
    num_rows,
    last_analyzed
FROM
    dba_tables
WHERE
    temporary = 'Y'
ORDER BY
    owner, table_name;

-- GTT를 현재 사용 중인 세션 확인
SELECT
    s.sid,
    s.username,
    s.status,
    o.object_name AS temp_table_name,
    o.object_type
FROM
    v$session s
    JOIN v$open_cursor oc ON s.sid = oc.sid
    JOIN dba_objects o ON UPPER(oc.sql_text) LIKE '%' || o.object_name || '%'
WHERE
    o.temporary = 'Y'
    AND o.object_type = 'TABLE';

-- 불필요한 GTT가 있다면 삭제 전 데이터 정리 후 DROP
-- STEP 1: 세션 데이터 확인
SELECT COUNT(*) FROM my_temp_table;  -- 해당 세션 데이터 확인

-- STEP 2: GTT 삭제
DROP TABLE my_unnecessary_temp_table;

-- STEP 3: 필요하다면 더 효율적인 구조로 재생성
CREATE GLOBAL TEMPORARY TABLE new_temp_table (
    id          NUMBER,
    data        VARCHAR2(200),
    created_dt  DATE DEFAULT SYSDATE
)
ON COMMIT DELETE ROWS;  -- 트랜잭션 종료 시 자동 데이터 삭제 (잠금 해제 유도)

예방 방법

1. 세션 및 잠금 모니터링 자동화

운영 환경에서 주기적으로 임시 테이블 잠금 수를 모니터링하는 자동화 스크립트나 Oracle Enterprise Manager(OEM) 알림을 구성하여 임계치 도달 전에 선제 대응할 수 있는 체계를 갖추어야 합니다. 아래와 같은 모니터링 쿼리를 5~10분 주기로 스케줄링하여 일정 수치 이상이면 DBA에게 알람을 발송하도록 구성하는 것이 실무에서 효과적입니다.

-- 모니터링용 임계치 확인 쿼리 (임시 테이블 잠금이 100 이상이면 경고)
SELECT
    CASE
        WHEN COUNT(*) >= 100 THEN 'WARNING: Temp Lock 수 임계치 초과 - ' || COUNT(*) || '개'
        WHEN COUNT(*) >= 50  THEN 'CAUTION: Temp Lock 수 주의 수준 - ' || COUNT(*) || '개'
        ELSE 'NORMAL: Temp Lock 수 정상 - ' || COUNT(*) || '개'
    END AS lock_status,
    COUNT(*) AS current_temp_locks
FROM
    v$lock
WHERE
    type = 'TT';

2. 애플리케이션 레벨의 트랜잭션 관리 강화

GTT를 사용하는 모든 애플리케이션 코드에서 반드시 예외 처리 블록 안에 COMMIT 또는 ROLLBACK을 명시적으로 호출하도록 코딩 표준을 수립하고 코드 리뷰 프로세스를 통해 관리해야 합니다. 특히 커넥션 풀 환경에서는 커넥션 반환 전 트랜잭션 상태를 초기화하는 로직을 반드시 포함시켜야 하며, ON COMMIT DELETE ROWS 옵션을 가진 GTT를 우선 사용하여 트랜잭션 종료 시 잠금이 자동 해제되도록 설계하는 것이 중요합니다.

에러 코드	설명
ORA-00054	`resource busy and acquire with NOWAIT specified` - 잠금 경합이 심할 때 NOWAIT 옵션 사용 시 발생하며 ORA-00057과 함께 나타날 수 있음
ORA-00060	`deadlock detected while waiting for resource` - 여러 세션 간 잠금 순환 대기로 발생하는 교착 상태로, GTT 과다 사용 환경에서 동반 발생 가능
ORA-01555	`snapshot too old` - GTT 사용 중 UNDO 공간 부족으로 발생하며 장시간 미종료 트랜잭션 상황에서 ORA-00057과 함께 나타나기도 함
ORA-04031	`unable to allocate shared memory` - SGA 메모리 부족 에러로, 대규모 GTT 운용 환경에서 간접적으로 연관됨

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

umzzil nng — Fri, 22 May 2026 00:01:39 +0000

ORA-00055: maximum number of DML locks exceeded 완벽 해결 가이드

ORA-00055란?

ORA-00055는 Oracle 데이터베이스에서 DML(Data Manipulation Language) 락(Lock)의 최대 허용 개수를 초과했을 때 발생하는 에러입니다. Oracle은 INSERT, UPDATE, DELETE, MERGE 등의 DML 작업 시 내부적으로 테이블 수준의 락(enqueue)을 획득하는데, 이 락의 총 개수가 초기화 파라미터 DML_LOCKS에서 정의한 한계치를 넘어서면 이 에러가 발생합니다. 주로 대규모 트랜잭션이 동시에 다수 발생하거나, 수많은 테이블에 걸친 복잡한 배치 작업이 수행될 때 현업 환경에서 종종 마주치게 되는 에러입니다.

주요 발생 원인

1. DML_LOCKS 파라미터 값이 너무 낮게 설정된 경우

DML_LOCKS 파라미터는 데이터베이스 전체에서 동시에 보유할 수 있는 DML 락의 최대 수를 결정합니다. 기본값은 4 * TRANSACTIONS 수식으로 자동 계산되지만, 수동으로 낮게 설정하거나 TRANSACTIONS 파라미터 자체가 실제 동시 접속 수에 비해 과소 산정된 경우 쉽게 한계에 도달합니다. 특히 레거시 시스템을 현대화하거나 사용자 수가 급증한 환경에서 파라미터를 재검토하지 않으면 이 문제가 빈번하게 발생합니다.

2. 하나의 트랜잭션에서 지나치게 많은 테이블에 DML을 수행하는 경우

Oracle은 하나의 트랜잭션 내에서 DML이 수행되는 각 테이블마다 별도의 테이블 레벨 락(TM Lock)을 획득합니다. 수십~수백 개의 테이블을 한 트랜잭션 내에서 처리하는 대형 배치 프로그램이나, 복잡한 연쇄 트리거(Cascading Trigger)가 다수의 테이블을 연속으로 수정하는 구조에서는 단일 세션만으로도 락 자원을 대량으로 소모할 수 있습니다. 애플리케이션 설계 시 트랜잭션 범위를 명확히 정의하지 않으면 이러한 문제가 누적됩니다.

3. 트랜잭션이 COMMIT/ROLLBACK 없이 장시간 열려 있는 경우

트랜잭션이 종료되지 않으면 해당 트랜잭션이 보유한 DML 락도 해제되지 않습니다. 애플리케이션 로직 오류, 네트워크 단절 후 세션이 정리되지 않은 상태(Inactive 세션), 또는 명시적 COMMIT 누락 등으로 인해 다수의 열린 트랜잭션이 쌓이면 전체 시스템의 DML 락 자원이 고갈됩니다. 장시간 수행되는 리포팅 쿼리와 DML이 혼재하는 OLTP 환경에서 특히 주의해야 합니다.

해결 방법

해결 1: 현재 DML_LOCKS 상태 확인 및 파라미터 증가

먼저 현재 파라미터 값과 실제 락 사용 현황을 확인합니다.

-- 현재 DML_LOCKS 파라미터 값 확인
SHOW PARAMETER DML_LOCKS;

-- 또는 V$PARAMETER 뷰로 확인
SELECT name, value, description
FROM   v$parameter
WHERE  name IN ('dml_locks', 'transactions');

-- 현재 시스템에서 사용 중인 DML 락 현황 확인
SELECT COUNT(*) AS current_dml_lock_count
FROM   v$lock
WHERE  type = 'TM';

-- 어느 세션이 TM 락을 많이 보유하고 있는지 확인
SELECT s.sid,
       s.serial#,
       s.username,
       s.status,
       s.program,
       COUNT(l.type) AS tm_lock_count
FROM   v$session s
JOIN   v$lock    l ON s.sid = l.sid
WHERE  l.type = 'TM'
GROUP BY s.sid, s.serial#, s.username, s.status, s.program
ORDER BY tm_lock_count DESC;

문제가 확인되면 파라미터를 조정합니다. DML_LOCKS는 동적 파라미터가 아니므로 반드시 재시작이 필요합니다.

-- DML_LOCKS 파라미터 증가 (spfile 적용, 재시작 필요)
-- 권장: 현재 TRANSACTIONS 값의 4~6배로 설정
ALTER SYSTEM SET DML_LOCKS = 10000 SCOPE = SPFILE;

-- TRANSACTIONS 파라미터도 함께 검토
ALTER SYSTEM SET TRANSACTIONS = 2500 SCOPE = SPFILE;

-- 변경 후 데이터베이스 재시작
-- SHUTDOWN IMMEDIATE;
-- STARTUP;

-- 재시작 후 적용 확인
SHOW PARAMETER DML_LOCKS;
SHOW PARAMETER TRANSACTIONS;

⚠️ 주의: DML_LOCKS = 0으로 설정하면 DML 락 기능 자체를 비활성화할 수 있지만, 이는 분산 트랜잭션 환경이나 특수한 경우에만 사용해야 하며 데이터 무결성 리스크가 있으므로 운영 환경에서는 권장하지 않습니다.

해결 2: 장시간 열린 트랜잭션(미종료 세션) 탐지 및 정리

-- 오랫동안 활성화된 트랜잭션 보유 세션 조회
SELECT s.sid,
       s.serial#,
       s.username,
       s.status,
       s.last_call_et          AS elapsed_sec,
       t.used_ublk             AS undo_blocks_used,
       t.start_time            AS tx_start_time,
       s.program,
       s.machine,
       s.sql_id
FROM   v$session     s
JOIN   v$transaction t ON s.taddr = t.addr
ORDER BY t.start_time ASC;

-- 특정 세션이 보유한 락 상세 조회
SELECT l.sid,
       l.type,
       l.lmode,
       l.request,
       o.object_name,
       o.object_type
FROM   v$lock        l
JOIN   dba_objects   o ON l.id1 = o.object_id
WHERE  l.type = 'TM'
AND    l.sid  = :target_sid   -- 문제 세션 SID 입력
ORDER BY o.object_name;

-- 문제 세션 강제 종료 (DBA 권한 필요)
-- 반드시 애플리케이션팀과 협의 후 수행할 것
ALTER SYSTEM KILL SESSION '&sid,&serial#' IMMEDIATE;

-- 예시: SID=245, SERIAL#=3921인 세션 종료
ALTER SYSTEM KILL SESSION '245,3921' IMMEDIATE;

해결 3: 대형 배치 트랜잭션을 분할하여 처리

하나의 트랜잭션에서 너무 많은 테이블을 처리하는 배치 작업을 분리합니다.

-- 나쁜 예: 수백 개 테이블을 하나의 트랜잭션으로 처리
BEGIN
    UPDATE table_001 SET status = 'Y' WHERE batch_date = TRUNC(SYSDATE);
    UPDATE table_002 SET status = 'Y' WHERE batch_date = TRUNC(SYSDATE);
    -- ... table_003 ~ table_200 까지 계속 ...
    UPDATE table_200 SET status = 'Y' WHERE batch_date = TRUNC(SYSDATE);
    COMMIT; -- 한 번에 COMMIT → 200개 TM 락 동시 보유
END;
/

-- 좋은 예: 일정 단위로 COMMIT을 분산
DECLARE
    v_commit_interval NUMBER := 10; -- 10개 테이블마다 COMMIT
    v_counter         NUMBER := 0;
BEGIN
    FOR rec IN (
        SELECT table_name
        FROM   user_tables
        WHERE  table_name LIKE 'TABLE_%'
        ORDER BY table_name
    ) LOOP
        EXECUTE IMMEDIATE
            'UPDATE ' || rec.table_name ||
            ' SET status = ''Y'' WHERE batch_date = TRUNC(SYSDATE)';

        v_counter := v_counter + 1;

        IF MOD(v_counter, v_commit_interval) = 0 THEN
            COMMIT; -- 주기적 COMMIT으로 TM 락 해제
        END IF;
    END LOOP;

    COMMIT; -- 나머지 처리
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE;
END;
/

해결 4: 트리거 내부의 불필요한 DML 최소화

-- 문제가 되는 트리거 패턴: 너무 많은 테이블에 연쇄 DML
CREATE OR REPLACE TRIGGER trg_order_after_insert
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
    -- 여러 테이블에 동시 DML → 각 테이블마다 TM 락 추가 발생
    INSERT INTO order_log    VALUES (:NEW.order_id, SYSDATE, 'INSERT');
    UPDATE inventory         SET qty = qty - :NEW.qty WHERE product_id = :NEW.product_id;
    UPDATE customer_summary  SET order_count = order_count + 1 WHERE cust_id = :NEW.cust_id;
    UPDATE daily_stats       SET total_orders = total_orders + 1 WHERE stat_date = TRUNC(SYSDATE);
    -- ... 추가 DML이 많을수록 위험
END;
/

-- 개선 방안: 트리거에서는 최소한의 DML만 수행하고
-- 나머지는 별도 배치 또는 비동기 처리로 이관
CREATE OR REPLACE TRIGGER trg_order_after_insert
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
    -- 트리거에서는 핵심 로그만 기록
    INSERT INTO order_log VALUES (:NEW.order_id, SYSDATE, 'INSERT');
    -- inventory, stats 업데이트는 별도 배치 잡으로 분리
END;
/

예방 방법

1. DML_LOCKS 파라미터 정기 모니터링 및 용량 계획 수립

운영 환경에서 DML 락 사용량을 주기적으로 수집하여 임계치 도달 전에 선제적으로 파라미터를 조정하는 프로세스를 마련해야 합니다. 아래 쿼리를 일별 배치로 실행하여 락 사용 추세를 지속적으로 추적하세요.

-- DML 락 사용률 모니터링 쿼리 (일별 배치 또는 알림 스크립트로 활용)
SELECT p.value                                    AS dml_locks_limit,
       COUNT(l.type)                              AS current_usage,
       ROUND(COUNT(l.type) / p.value * 100, 2)   AS usage_pct,
       CASE
           WHEN ROUND(COUNT(l.type) / p.value * 100, 2) >= 80
           THEN '⚠️ 경고: DML_LOCKS 80% 초과 - 즉시 검토 필요'
           WHEN ROUND(COUNT(l.type) / p.value * 100, 2) >= 60
           THEN '주의: DML_LOCKS 60% 초과'
           ELSE '정상'
       END AS status_msg
FROM   v$parameter p,
       v$lock      l
WHERE  p.name  = 'dml_locks'
AND    l.type(+) = 'TM'
GROUP BY p.value;

사용률이 60%를 넘으면 검토, 80%를 넘으면 즉각 파라미터 증가를 고려하는 운영 기준을 팀 내에 수립하길 권장합니다.

2. 애플리케이션 트랜잭션 설계 원칙 수립 및 코드 리뷰 프로세스 강화

ORA-00055의 근본적인 예방은 개발 단계에서 트랜잭션 범위를 적절히 제어하는 것입니다. 개발 팀에 다음 원칙을 가이드라인으로 배포하세요.


sql
-- 트랜잭션 범위 검증 쿼리 (개발/테스트 단계에서 실행)
-- 특정 세션이 현재 보유 중인 TM 락 수 확인
SELECT s.sid,
       s.username,
       s.program,
       COUNT(*)        AS tm_lock_count,
       LISTAGG(o.object_name, ', ')
           WITHIN GROUP (ORDER BY o

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

umzzil nng — Thu, 21 May 2026 23:35:36 +0000

ORA-00054 완벽 가이드: resource busy and acquire with NOWAIT specified

ORA-00054란?

ORA-00054는 Oracle 데이터베이스에서 특정 리소스(주로 테이블이나 행)에 대한 잠금(Lock)을 획득하려 했지만, 해당 리소스가 이미 다른 세션에 의해 점유되어 있을 때 발생하는 에러입니다. 특히 NOWAIT 옵션이 지정된 상태에서 잠금 획득에 실패했을 때 즉시 에러를 반환하며, DDL 문장(ALTER TABLE, DROP TABLE 등) 실행 시 해당 객체에 DML 트랜잭션이 진행 중인 경우에도 빈번하게 발생합니다. 실무 환경에서는 배치 작업, 스키마 변경 작업, 또는 애플리케이션의 동시 접근이 많은 환경에서 특히 자주 마주치는 에러입니다.

주요 발생 원인

DDL 작업 중 활성 트랜잭션 존재
운영 중인 테이블에 ALTER TABLE, DROP TABLE, TRUNCATE TABLE 등의 DDL 명령을 실행할 때, 해당 테이블에 커밋되지 않은 DML(INSERT, UPDATE, DELETE) 트랜잭션이 존재하면 Oracle은 배타적 잠금을 획득할 수 없어 이 에러를 반환합니다. Oracle DDL은 내부적으로 NOWAIT 방식으로 잠금을 시도하기 때문에 대기 없이 즉시 에러가 발생합니다.
SELECT ... FOR UPDATE NOWAIT 사용 시 행 잠금 충돌
애플리케이션 코드에서 SELECT ... FOR UPDATE NOWAIT 구문을 사용할 때, 조회하려는 행이 이미 다른 세션에 의해 잠겨 있다면 대기 없이 즉시 ORA-00054가 발생합니다. 이는 애플리케이션의 낙관적 잠금(Optimistic Locking) 전략 또는 빠른 실패(Fail-Fast) 패턴에서 의도적으로 사용하기도 하지만, 예상치 못한 충돌 시에는 에러 처리가 반드시 필요합니다.
장시간 실행되는 트랜잭션 방치
개발자 또는 운영자가 SQL*Plus나 SQL Developer에서 DML을 실행한 후 COMMIT 또는 ROLLBACK 없이 세션을 그대로 두는 경우, 해당 잠금이 계속 유지됩니다. 이 상태에서 다른 작업자가 동일한 테이블에 DDL을 시도하거나 NOWAIT 잠금을 요청하면 ORA-00054가 발생하며, 야간 배치 작업이나 스키마 변경 작업 시 특히 문제가 됩니다.
자동화 배치 프로세스 간 충돌
여러 배치 프로세스가 동일한 테이블에 동시에 접근하는 구조에서 실행 시간이 겹치면 잠금 충돌이 발생할 수 있습니다. 특히 스케줄러 설정 오류로 인해 중복 실행되거나, 이전 배치가 완료되기 전에 다음 배치가 시작되는 경우에 ORA-00054가 빈번하게 나타납니다.
온라인 스키마 변경 미지원 환경에서의 DDL 실행
Oracle 11g 이상에서는 ONLINE 옵션으로 일부 DDL을 무중단으로 처리할 수 있지만, 이를 모르거나 지원하지 않는 DDL을 실행할 때 잠금 충돌이 발생합니다. 특히 인덱스 생성/재빌드, 파티션 작업 등에서 배타적 잠금이 필요한 경우 활성 트랜잭션과 충돌하여 에러가 발생합니다.

해결 방법

1단계: 현재 잠금 보유 세션 파악

가장 먼저 어떤 세션이 해당 리소스를 점유하고 있는지 확인해야 합니다.

-- 현재 활성화된 잠금 정보 조회
SELECT
    s.sid,
    s.serial#,
    s.username,
    s.status,
    s.osuser,
    s.machine,
    s.program,
    s.logon_time,
    l.type        AS lock_type,
    l.mode_held,
    l.mode_requested,
    o.object_name,
    o.object_type
FROM
    v$session   s
    JOIN v$lock l ON s.sid = l.sid
    JOIN dba_objects o ON l.id1 = o.object_id
WHERE
    l.type IN ('TM', 'TX')
    AND s.status = 'ACTIVE'
ORDER BY
    s.logon_time;

-- 특정 테이블에 대한 잠금 세션 조회
SELECT
    s.sid,
    s.serial#,
    s.username,
    s.status,
    s.machine,
    s.program,
    s.sql_id,
    s.last_call_et AS elapsed_seconds,
    o.object_name
FROM
    v$session   s
    JOIN v$lock l ON s.sid = l.sid
    JOIN dba_objects o ON l.id1 = o.object_id
WHERE
    o.object_name = 'YOUR_TABLE_NAME'  -- 대상 테이블명 입력
    AND l.type = 'TM';

2단계: 블로킹 세션과 대기 세션 연관 조회

-- 블로킹 세션과 대기 세션을 함께 조회
SELECT
    blocker.sid          AS blocker_sid,
    blocker.serial#      AS blocker_serial,
    blocker.username     AS blocker_user,
    blocker.status       AS blocker_status,
    blocker.machine      AS blocker_machine,
    blocker.program      AS blocker_program,
    waiter.sid           AS waiter_sid,
    waiter.serial#       AS waiter_serial,
    waiter.username      AS waiter_user,
    waiter.status        AS waiter_status,
    waiter.sql_id        AS waiter_sql_id
FROM
    v$session blocker
    JOIN v$session waiter ON blocker.sid = waiter.blocking_session
WHERE
    waiter.blocking_session IS NOT NULL;

3단계: 해당 세션이 실행 중인 SQL 확인

-- 블로킹 세션의 현재/최근 실행 SQL 확인
SELECT
    s.sid,
    s.serial#,
    s.username,
    q.sql_text,
    q.sql_fulltext
FROM
    v$session s
    JOIN v$sql q ON s.sql_id = q.sql_id
WHERE
    s.sid = &블로킹_SID;

4단계: 세션 종료 또는 COMMIT/ROLLBACK 유도

-- 방법 1: 해당 세션에 COMMIT 또는 ROLLBACK 요청 (DBA가 직접 불가, 세션 소유자에게 요청)

-- 방법 2: DBA 권한으로 세션 강제 종료 (최후의 수단)
-- 반드시 비즈니스 영향도 확인 후 실행!
ALTER SYSTEM KILL SESSION '&SID,&SERIAL#' IMMEDIATE;

-- 예시
ALTER SYSTEM KILL SESSION '142,38291' IMMEDIATE;

-- OS 레벨에서도 종료가 필요한 경우 (DISCONNECT SESSION)
ALTER SYSTEM DISCONNECT SESSION '142,38291' IMMEDIATE;

5단계: NOWAIT 대신 WAIT 옵션 사용

애플리케이션 코드에서 NOWAIT 대신 일정 시간 대기하는 방식으로 변경합니다.

-- 기존 방식 (즉시 에러 발생)
SELECT *
FROM   employees
WHERE  department_id = 10
FOR UPDATE NOWAIT;

-- 개선 방식 1: 특정 시간(초) 대기 후 실패
SELECT *
FROM   employees
WHERE  department_id = 10
FOR UPDATE WAIT 5;  -- 5초 대기 후 실패

-- 개선 방식 2: 무한 대기 (기본값, 주의 필요)
SELECT *
FROM   employees
WHERE  department_id = 10
FOR UPDATE;

6단계: DDL 작업 전 잠금 세션 점검 스크립트

-- DDL 실행 전 해당 테이블의 활성 세션 및 미완료 트랜잭션 점검
DECLARE
    v_table_name VARCHAR2(100) := 'YOUR_TABLE_NAME';
    v_count      NUMBER;
BEGIN
    SELECT COUNT(*)
    INTO   v_count
    FROM   v$session   s
           JOIN v$lock l ON s.sid = l.sid
           JOIN dba_objects o ON l.id1 = o.object_id
    WHERE  o.object_name = UPPER(v_table_name)
           AND l.type = 'TM'
           AND s.status = 'ACTIVE';

    IF v_count > 0 THEN
        DBMS_OUTPUT.PUT_LINE('경고: ' || v_table_name || ' 테이블에 ' || v_count || '개의 활성 잠금이 존재합니다.');
        DBMS_OUTPUT.PUT_LINE('DDL 실행 전 해당 세션의 트랜잭션을 완료하십시오.');
    ELSE
        DBMS_OUTPUT.PUT_LINE('안전: 활성 잠금 없음. DDL 실행 가능합니다.');
    END IF;
END;
/

7단계: 인덱스/DDL ONLINE 옵션 활용

-- 기존 방식 (배타적 잠금 필요, ORA-00054 위험)
CREATE INDEX idx_emp_dept ON employees(department_id);

-- 개선 방식: ONLINE 옵션으로 DML 허용하면서 인덱스 생성
CREATE INDEX idx_emp_dept ON employees(department_id) ONLINE;

-- 인덱스 재빌드도 ONLINE 옵션 활용
ALTER INDEX idx_emp_dept REBUILD ONLINE;

예방 방법

1. 트랜잭션 관리 정책 수립 및 자동 COMMIT 방지

모든 DML 작업은 반드시 명시적으로 COMMIT 또는 ROLLBACK을 수행하도록 개발 가이드라인을 수립해야 합니다. SQL*Plus나 SQL Developer 사용 시 세션을 종료하기 전 반드시 트랜잭션을 완료하는 습관을 팀 내 공유하고, 가능하면 자동 세션 종료 타임아웃을 설정합니다.

-- 유휴 세션 자동 종료 프로파일 설정 (30분 유휴 시 세션 종료)
CREATE PROFILE limited_session LIMIT
    IDLE_TIME          30
    CONNECT_TIME       480
    SESSIONS_PER_USER  5;

-- 사용자에게 프로파일 적용
ALTER USER app_user PROFILE limited_session;

2. DDL 작업 전 표준 점검 절차(SOP) 수립

스키마 변경 작업을 수행하기 전에 반드시 활성 트랜잭션 여부를 확인하는 표준 절차를 만들고, 가능하면 저트래픽 시간대(새벽 또는 주말)에 DDL을 수행하도록 정책을 수립합니다. 또한 Oracle의 LOCK_TIMEOUT 관련 파라미터를 활용하거나, DDL 전에 DBMS_LOCK 패키지를 이용한 커스텀 잠금 관리를 검토합니다.

-- DDL 실행 전 활성 세션 전체 점검 스크립트 (DBA용)
SELECT
    s.sid,
    s.serial#,
    s.username,
    s.status,
    s.last_call_et  AS idle_seconds,
    s.machine,
    s.program,
    t.used_ublk     AS undo_blocks_used
FROM
    v$session     s
    LEFT JOIN v$transaction t ON s.taddr = t.addr
WHERE
    s.status = 'ACTIVE'
    AND s.type = 'USER'
    AND t.addr IS NOT NULL  -- 활성 트랜잭션이 있는 세션만
ORDER BY
    t.used_ublk DESC;

3. 애플리케이션 레벨 에러 처리 및 재시도 로직 구현

NOWAIT 옵션을 사용하는 코드에서는 ORA-00054 예외를 반드시 캐치하고, 적절한 재시도 로직 또는 사용자 친화적 메시지를 제공해야 합니다. Java, Python 등 애플리케이션 레이어에서 재시도 횟수와 대기 시간을 설정하는 패

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

umzzil nng — Thu, 21 May 2026 23:26:07 +0000

ORA-00051: timeout occurred while waiting for a resource

ORA-00051란?

ORA-00051은 Oracle 데이터베이스에서 특정 리소스를 기다리는 도중 설정된 타임아웃 시간이 초과되었을 때 발생하는 에러입니다. 주로 분산 트랜잭션(Distributed Transaction) 환경에서 원격 데이터베이스와의 통신 중 락(Lock)이나 리소스 할당을 기다리다가 응답이 없을 때 나타납니다. 이 에러는 단순한 네트워크 지연부터 데드락(Deadlock), 잘못된 DB Link 설정, 과부하 상태의 시스템에 이르기까지 다양한 원인으로 인해 트리거될 수 있기 때문에, 발생 원인을 정확히 파악하고 대응하는 것이 중요합니다.

주요 발생 원인

1. 분산 트랜잭션(DB Link)에서의 락 대기 타임아웃

Oracle DB Link를 통해 원격 데이터베이스에 접근할 때, 원격 측에서 특정 리소스(테이블, 로우 등)에 대한 락을 보유한 세션이 있으면 현재 세션은 대기 상태에 들어갑니다. 이 대기 시간이 DISTRIBUTED_LOCK_TIMEOUT 파라미터에 설정된 값을 초과하면 ORA-00051이 발생합니다.

2. DISTRIBUTED_LOCK_TIMEOUT 파라미터 값이 너무 낮게 설정된 경우

DISTRIBUTED_LOCK_TIMEOUT 초기화 파라미터는 분산 트랜잭션에서 락을 기다리는 최대 시간을 초(second) 단위로 지정합니다. 기본값은 60초이며, 네트워크 지연이 잦거나 복잡한 분산 처리를 수행하는 환경에서는 이 값이 너무 낮게 설정되어 정상적인 작업도 타임아웃으로 종료될 수 있습니다.

3. 네트워크 불안정 및 원격 DB 응답 지연

로컬 DB와 원격 DB 사이의 네트워크 패킷 손실, 지연 또는 방화벽 세션 만료 등으로 인해 원격 DB로부터 응답이 지연될 수 있습니다. 이 경우 Oracle은 리소스를 할당받지 못한 상태에서 타임아웃을 발생시켜 ORA-00051을 반환합니다.

4. 장기 실행 트랜잭션으로 인한 락 충돌

원격 DB 혹은 로컬 DB의 특정 세션이 커밋(Commit)이나 롤백(Rollback)을 하지 않은 채 락을 오랫동안 유지하는 경우, 다른 세션의 대기가 길어지고 타임아웃으로 이어질 수 있습니다. 특히 배치 프로그램이나 장기 DML 작업 이후 명시적으로 트랜잭션을 종료하지 않은 상황에서 자주 발생합니다.

5. In-doubt 트랜잭션(분산 트랜잭션 불완전 종료)

분산 트랜잭션 도중 네트워크 단절이나 원격 DB 장애로 인해 트랜잭션이 완전히 커밋 또는 롤백되지 못하고 "In-doubt" 상태로 남는 경우가 있습니다. 이런 In-doubt 트랜잭션은 관련 리소스에 대한 락을 계속 보유하여 이후 동일 리소스에 접근하는 세션에서 ORA-00051을 유발할 수 있습니다.

해결 방법

1. DISTRIBUTED_LOCK_TIMEOUT 파라미터 확인 및 조정

먼저 현재 설정값을 확인하고, 업무 특성에 맞게 적절한 값으로 조정합니다.

-- 현재 DISTRIBUTED_LOCK_TIMEOUT 값 확인
SHOW PARAMETER DISTRIBUTED_LOCK_TIMEOUT;

-- 또는 V$PARAMETER 뷰에서 확인
SELECT NAME, VALUE, DESCRIPTION
FROM   V$PARAMETER
WHERE  NAME = 'distributed_lock_timeout';

-- 시스템 레벨에서 타임아웃 값 변경 (예: 120초로 증가)
-- 동적 파라미터이므로 재시작 없이 적용 가능
ALTER SYSTEM SET DISTRIBUTED_LOCK_TIMEOUT = 120;

-- 변경 후 확인
SELECT NAME, VALUE
FROM   V$PARAMETER
WHERE  NAME = 'distributed_lock_timeout';

⚠️ 값을 무작정 높이는 것은 근본적인 해결책이 아닙니다. 반드시 원인 분석과 병행하여 조정하세요.

2. 현재 락 대기 세션 및 블로킹 세션 확인

-- 락 대기 중인 세션 조회
SELECT
    s.SID,
    s.SERIAL#,
    s.USERNAME,
    s.STATUS,
    s.WAIT_CLASS,
    s.EVENT,
    s.SECONDS_IN_WAIT,
    s.SQL_ID,
    s.MACHINE,
    s.PROGRAM
FROM
    V$SESSION s
WHERE
    s.WAIT_CLASS != 'Idle'
    AND s.STATUS = 'ACTIVE'
ORDER BY
    s.SECONDS_IN_WAIT DESC;

-- 블로킹 세션과 대기 세션 간의 관계 파악
SELECT
    DECODE(request, 0, 'Holder: ', 'Waiter: ') || s.SID AS SID_INFO,
    s.USERNAME,
    s.MACHINE,
    s.PROGRAM,
    l.TYPE,
    l.ID1,
    l.ID2,
    l.LMODE,
    l.REQUEST,
    l.CTIME,
    s.SQL_ID
FROM
    V$LOCK l
    JOIN V$SESSION s ON l.SID = s.SID
WHERE
    (l.ID1, l.ID2, l.TYPE) IN (
        SELECT ID1, ID2, TYPE
        FROM   V$LOCK
        WHERE  REQUEST > 0
    )
ORDER BY
    l.ID1, l.ID2, l.REQUEST;

3. 블로킹 세션 강제 종료

원인 세션을 파악한 후, 업무 영향도를 검토하고 필요한 경우 강제 종료합니다.

-- 특정 세션 강제 종료 (SID와 SERIAL# 확인 후 실행)
ALTER SYSTEM KILL SESSION '블로킹_SID, 블로킹_SERIAL#' IMMEDIATE;

-- 예시
ALTER SYSTEM KILL SESSION '145, 3821' IMMEDIATE;

-- 세션 종료 후 상태 확인
SELECT SID, SERIAL#, STATUS, USERNAME
FROM   V$SESSION
WHERE  SID = 145;

4. In-doubt 트랜잭션 확인 및 처리

-- In-doubt 분산 트랜잭션 조회
SELECT
    LOCAL_TRAN_ID,
    GLOBAL_TRAN_ID,
    STATE,
    MIXED,
    ADVICE,
    TRAN_COMMENT,
    FAIL_TIME,
    FORCE_TIME
FROM
    DBA_2PC_PENDING;

-- In-doubt 트랜잭션을 강제로 커밋 처리 (DBA 판단 후 실행)
-- STATE가 'prepared'인 경우, 원격 DB와 협의 후 결정
COMMIT FORCE 'LOCAL_TRAN_ID 값';

-- 예시
COMMIT FORCE '1.35.12';

-- 또는 강제 롤백
ROLLBACK FORCE '1.35.12';

-- 처리 후 DBA_2PC_PENDING에서 제거 확인
SELECT COUNT(*)
FROM   DBA_2PC_PENDING;

-- DBMS_TRANSACTION 패키지를 사용한 정리 (Oracle 권장)
EXEC DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('LOCAL_TRAN_ID 값');

5. DB Link 연결 상태 확인

-- 현재 사용 중인 DB Link 목록 확인
SELECT DB_LINK, USERNAME, HOST, CREATED
FROM   DBA_DB_LINKS;

-- 활성화된 DB Link 세션 확인
SELECT
    s.SID,
    s.SERIAL#,
    s.USERNAME,
    s.STATUS,
    d.DB_LINK,
    d.HETEROGENEOUS,
    d.PROTOCOL,
    d.OPEN_CURSORS
FROM
    V$SESSION s
    JOIN V$DBLINK d ON s.SID = d.SID;

-- DB Link 연결 테스트
SELECT * FROM DUAL@원격_DB_LINK명;

6. 장기 미완료 트랜잭션 확인

-- 오래된 활성 트랜잭션 확인 (1시간 이상)
SELECT
    s.SID,
    s.SERIAL#,
    s.USERNAME,
    s.STATUS,
    t.START_TIME,
    ROUND((SYSDATE - TO_DATE(t.START_TIME, 'MM/DD/YY HH24:MI:SS')) * 24 * 60, 2) AS ELAPSED_MIN,
    t.USED_UBLK,
    t.LOG_IO,
    s.SQL_ID
FROM
    V$TRANSACTION t
    JOIN V$SESSION s ON t.SES_ADDR = s.SADDR
WHERE
    (SYSDATE - TO_DATE(t.START_TIME, 'MM/DD/YY HH24:MI:SS')) * 24 * 60 > 60
ORDER BY
    ELAPSED_MIN DESC;

예방 방법

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

umzzil nng — Thu, 21 May 2026 06:05:17 +0000

ORA-01031란?

ORA-01031: insufficient privileges 에러는 현재 데이터베이스 세션의 사용자가 요청한 작업을 수행하기 위한 권한이 충분하지 않을 때 발생하는 Oracle 에러입니다. 단순한 테이블 조회부터 DDL 실행, 시스템 관리 작업에 이르기까지 권한이 없는 모든 작업에서 발생할 수 있으며, 특히 운영 환경에서 계정 관리가 미흡할 때 자주 마주치는 에러입니다. 개발 초기에는 DBA 권한으로 작업하다가 운영 계정으로 전환하거나, 롤(Role) 기반 권한이 세션 컨텍스트에서 비활성화된 경우에도 동일한 에러가 발생할 수 있습니다.

주요 발생 원인

객체에 대한 직접 권한 미부여
특정 테이블, 뷰, 프로시저 등에 대해 SELECT, INSERT, UPDATE, DELETE, EXECUTE 등의 권한이 해당 사용자에게 직접 부여되지 않은 경우입니다. 특히 다른 스키마 소유의 객체에 접근할 때 자주 발생하며, 개발자가 자신의 스키마가 아닌 타 스키마 테이블에 쿼리를 날릴 때 흔히 겪는 상황입니다.
롤(Role)을 통한 권한 부여 후 스토어드 프로그램에서 사용 시 충돌
Oracle에서는 저장 프로시저(Stored Procedure), 함수(Function), 패키지(Package) 내부에서는 롤을 통해 부여된 권한이 적용되지 않습니다. 직접 권한(Direct Grant)만 인식하기 때문에, 롤로만 권한을 받은 사용자가 PL/SQL 블록 내에서 해당 객체에 접근하면 ORA-01031이 발생합니다.
시스템 권한 미부여 (CREATE, ALTER, DROP 등)
DDL 작업(테이블 생성, 인덱스 생성, 뷰 생성 등)을 수행하려면 해당 시스템 권한이 필요합니다. CREATE TABLE, CREATE VIEW, CREATE PROCEDURE 등의 권한 없이 DDL을 실행하면 이 에러가 발생하며, 특히 신규 계정 생성 직후 기본 권한만 부여된 상태에서 자주 발생합니다.
SYSDBA / SYSOPER 권한 없이 관리자 접속 시도
CONNECT / AS SYSDBA 형태로 접속하려 할 때 해당 OS 사용자가 dba 그룹에 포함되어 있지 않거나, password file에 등록되지 않은 경우에 발생합니다. 원격 접속 시 password file이 존재하지 않아도 동일한 에러가 발생할 수 있습니다.
INVOKER RIGHTS vs DEFINER RIGHTS 혼동
기본적으로 PL/SQL 객체는 DEFINER RIGHTS 방식으로 동작하여 소유자의 권한으로 실행됩니다. 그러나 AUTHID CURRENT_USER로 선언된 INVOKER RIGHTS 프로시저에서는 호출자의 권한을 기준으로 실행되므로, 호출자에게 권한이 없으면 ORA-01031이 발생합니다.

해결 방법

원인 1: 객체 권한 직접 부여

다른 스키마의 테이블이나 객체에 접근 권한을 부여합니다.

-- HR 스키마의 EMPLOYEES 테이블에 대해 APP_USER에게 SELECT 권한 부여
GRANT SELECT ON hr.employees TO app_user;

-- INSERT, UPDATE, DELETE 권한 함께 부여
GRANT SELECT, INSERT, UPDATE, DELETE ON hr.employees TO app_user;

-- 특정 컬럼에만 UPDATE 권한 부여 (컬럼 레벨 권한)
GRANT UPDATE (salary, job_id) ON hr.employees TO app_user;

-- 프로시저 실행 권한 부여
GRANT EXECUTE ON hr.process_employee TO app_user;

-- 권한 부여 후 확인
SELECT grantee, table_name, privilege, grantable
FROM dba_tab_privs
WHERE grantee = 'APP_USER'
ORDER BY table_name, privilege;

원인 2: PL/SQL 내 롤 권한 문제 해결

저장 프로그램 내부에서 롤로 부여된 권한은 동작하지 않으므로 직접 권한을 부여합니다.

-- 문제 상황: DEVELOPER_ROLE을 통해 권한을 받은 경우
-- PL/SQL 내부에서 hr.employees 접근 불가

-- 잘못된 방식 (롤을 통한 권한 - PL/SQL 내부에서 미동작)
GRANT SELECT ON hr.employees TO developer_role;
GRANT developer_role TO app_user;

-- 올바른 방식 (직접 권한 부여)
GRANT SELECT ON hr.employees TO app_user;

-- 현재 세션에서 활성화된 롤 확인
SELECT * FROM session_roles;

-- 사용자에게 직접 부여된 권한 확인
SELECT grantee, owner, table_name, privilege
FROM dba_tab_privs
WHERE grantee = 'APP_USER';

-- 롤을 통해 부여된 권한 확인 (PL/SQL 내에서는 미적용)
SELECT role, owner, table_name, privilege
FROM role_tab_privs
WHERE role IN (
    SELECT granted_role FROM dba_role_privs WHERE grantee = 'APP_USER'
);

원인 3: 시스템 권한 부여

DDL 실행에 필요한 시스템 권한을 부여합니다.

-- 기본 개발 계정 생성 및 권한 부여 예시
CREATE USER dev_user IDENTIFIED BY "SecurePass123!";

-- 접속 권한 부여
GRANT CREATE SESSION TO dev_user;

-- 객체 생성 관련 시스템 권한 부여
GRANT CREATE TABLE TO dev_user;
GRANT CREATE VIEW TO dev_user;
GRANT CREATE PROCEDURE TO dev_user;
GRANT CREATE SEQUENCE TO dev_user;
GRANT CREATE TRIGGER TO dev_user;
GRANT CREATE SYNONYM TO dev_user;

-- 테이블스페이스 쿼터 설정 (UNLIMITED 주의 - 운영환경에서는 제한 권장)
ALTER USER dev_user QUOTA 500M ON users;

-- 부여된 시스템 권한 전체 확인
SELECT grantee, privilege, admin_option
FROM dba_sys_privs
WHERE grantee = 'DEV_USER'
ORDER BY privilege;

-- 특정 권한 회수
REVOKE CREATE TABLE FROM dev_user;

원인 4: SYSDBA 접속 문제 해결

-- [OS 레벨] oracle 유저가 dba 그룹에 포함되어 있는지 확인 (Linux/Unix)
-- $ id oracle
-- uid=54321(oracle) gid=54321(oinstall) groups=54321(oinstall),54322(dba)

-- password file 존재 여부 확인 (Oracle 19c 기준)
-- $ORACLE_HOME/dbs/orapw{SID} 파일 존재 여부 확인

-- password file 재생성 (SYS 비밀번호 재설정)
-- $ orapwd file=$ORACLE_HOME/dbs/orapwORCL password=새비밀번호 entries=10

-- 원격 SYSDBA 접속 허용 여부 확인
SHOW PARAMETER REMOTE_LOGIN_PASSWORDFILE;

-- 필요 시 파라미터 변경 (EXCLUSIVE 또는 SHARED)
-- ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE SCOPE=SPFILE;

-- SYSDBA 권한을 가진 사용자 목록 확인
SELECT * FROM v$pwfile_users;

-- 특정 사용자에게 SYSDBA 권한 부여
GRANT SYSDBA TO dba_user;

원인 5: INVOKER RIGHTS 프로시저 권한 문제

-- INVOKER RIGHTS 프로시저 예시 (AUTHID CURRENT_USER)
CREATE OR REPLACE PROCEDURE get_employee_info(p_emp_id IN NUMBER)
AUTHID CURRENT_USER  -- 호출자 권한으로 실행
AS
    v_name VARCHAR2(100);
BEGIN
    -- 호출자에게 hr.employees SELECT 권한이 없으면 ORA-01031 발생
    SELECT first_name || ' ' || last_name
    INTO v_name
    FROM hr.employees
    WHERE employee_id = p_emp_id;

    DBMS_OUTPUT.PUT_LINE('직원명: ' || v_name);
END;
/

-- 해결책 1: 호출자에게 직접 권한 부여
GRANT SELECT ON hr.employees TO calling_user;

-- 해결책 2: DEFINER RIGHTS 방식으로 변경 (기본값)
CREATE OR REPLACE PROCEDURE get_employee_info(p_emp_id IN NUMBER)
-- AUTHID DEFINER (기본값, 생략 가능)
AS
    v_name VARCHAR2(100);
BEGIN
    SELECT first_name || ' ' || last_name
    INTO v_name
    FROM hr.employees
    WHERE employee_id = p_emp_id;

    DBMS_OUTPUT.PUT_LINE('직원명: ' || v_name);
END;
/

권한 문제 전반 진단 쿼리


sql
-- 특정 사용자의 모든 권한 현황 종합 조회
SELECT 'SYSTEM PRIVILEGE' AS priv_type,
       privilege AS privilege_name,
       NULL AS object_name,
       admin_option AS extra_option

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

umzzil nng — Thu, 21 May 2026 05:49:06 +0000

ORA-01017 완벽 해결 가이드: invalid username/password; logon denied

ORA-01017란?

ORA-01017은 Oracle 데이터베이스에 접속을 시도할 때 입력한 사용자명(Username) 또는 비밀번호(Password)가 올바르지 않아 로그인이 거부될 때 발생하는 에러입니다. 단순히 비밀번호를 잘못 입력한 경우뿐만 아니라, 계정 잠금, 대소문자 구분 설정, 잘못된 접속 문자열 등 다양한 원인으로 발생할 수 있어 실무에서 가장 빈번하게 마주치는 에러 중 하나입니다. 특히 Oracle 11g 이후부터 비밀번호 대소문자 구분(Case Sensitive)이 기본값으로 활성화되어, 기존 방식으로 접속하던 개발자나 DBA가 업그레이드 후 이 에러를 겪는 사례가 매우 많습니다.

주요 발생 원인

1. 비밀번호 대소문자 구분 문제 (Case Sensitivity)

Oracle 11g부터 SEC_CASE_SENSITIVE_LOGON 파라미터가 기본적으로 TRUE로 설정되어 비밀번호가 대소문자를 구분합니다. 이전 버전(10g 이하)에서 마이그레이션하거나, 비밀번호 설정 당시의 대소문자를 정확히 기억하지 못하는 경우 로그인에 실패하게 됩니다.

2. 계정 잠금(Account Lock) 상태

비밀번호를 여러 번 틀리거나 프로파일의 FAILED_LOGIN_ATTEMPTS 설정값을 초과하면 계정이 자동으로 잠깁니다. 잠긴 계정은 올바른 비밀번호를 입력해도 ORA-01017과 함께 접속이 거부되며, 이 경우 ORA-28000 에러가 함께 발생하기도 합니다.

3. 비밀번호 만료(Password Expired)

Oracle 프로파일의 PASSWORD_LIFE_TIME 설정에 의해 비밀번호 유효기간이 지난 경우 로그인이 차단됩니다. 특히 DEFAULT 프로파일의 PASSWORD_LIFE_TIME이 180일로 설정된 운영 환경에서 주기적으로 발생할 수 있으며, 이 경우 ORA-28001 에러와 함께 나타납니다.

4. 잘못된 접속 문자열 또는 환경변수 설정

JDBC, Python, Shell Script 등 애플리케이션에서 접속 문자열 내 사용자명이나 비밀번호가 잘못 설정된 경우 발생합니다. 특히 비밀번호에 특수문자(@, !, # 등)가 포함된 경우 이스케이프 처리를 하지 않아 접속에 실패하는 사례가 매우 흔합니다.

5. 잘못된 데이터베이스 링크(Database Link) 설정

DB Link를 통해 원격 데이터베이스에 접속할 때, 원격 DB의 계정 정보가 변경되었음에도 DB Link가 이전 정보를 그대로 사용하고 있는 경우 ORA-01017이 발생합니다. 비밀번호 변경 후 관련 DB Link를 재생성하지 않은 경우가 대표적입니다.

해결 방법

① 비밀번호 대소문자 구분 확인 및 해결

현재 대소문자 구분 설정을 확인하고, 필요 시 임시로 비활성화할 수 있습니다.

-- 현재 대소문자 구분 설정 확인
SHOW PARAMETER SEC_CASE_SENSITIVE_LOGON;

-- 대소문자 구분 비활성화 (임시 조치, 보안상 권장하지 않음)
ALTER SYSTEM SET SEC_CASE_SENSITIVE_LOGON = FALSE;

-- 비밀번호를 명확하게 대소문자 포함하여 재설정
ALTER USER scott IDENTIFIED BY "NewPass#2024";

-- 설정 복원 (보안을 위해 반드시 원복)
ALTER SYSTEM SET SEC_CASE_SENSITIVE_LOGON = TRUE;

⚠️ 대소문자 구분 비활성화는 보안 취약점을 유발할 수 있으므로, 가급적 비밀번호를 재설정하는 방향으로 해결하는 것을 권장합니다.

② 계정 잠금 해제

-- 계정 잠금 여부 확인
SELECT username, account_status, lock_date, expiry_date
FROM dba_users
WHERE username = 'SCOTT';

-- 계정 잠금 해제
ALTER USER scott ACCOUNT UNLOCK;

-- 잠금 해제 후 비밀번호도 함께 재설정 (권장)
ALTER USER scott IDENTIFIED BY "NewSecurePass#1" ACCOUNT UNLOCK;

-- 프로파일의 FAILED_LOGIN_ATTEMPTS 설정 확인
SELECT profile, resource_name, limit
FROM dba_profiles
WHERE resource_name = 'FAILED_LOGIN_ATTEMPTS'
ORDER BY profile;

③ 비밀번호 만료 해결

-- 비밀번호 만료 상태 확인
SELECT username, account_status, expiry_date
FROM dba_users
WHERE account_status LIKE '%EXPIRED%';

-- 비밀번호 재설정으로 만료 해제
ALTER USER scott IDENTIFIED BY "NewPass#2024";

-- 특정 계정의 비밀번호 만료 기한 무제한으로 변경 (서비스 계정 권장)
-- 먼저 전용 프로파일 생성
CREATE PROFILE app_service_profile LIMIT
  PASSWORD_LIFE_TIME UNLIMITED
  FAILED_LOGIN_ATTEMPTS 10
  PASSWORD_LOCK_TIME 1/24;

-- 프로파일 적용
ALTER USER app_user PROFILE app_service_profile;

-- 적용 결과 확인
SELECT username, profile FROM dba_users WHERE username = 'APP_USER';

④ 특수문자가 포함된 비밀번호 접속 방법

비밀번호에 특수문자가 포함된 경우, sqlplus에서는 큰따옴표로 감싸서 처리합니다.

-- sqlplus 접속 시 특수문자 포함 비밀번호 처리
-- 잘못된 방법 (@ 문자가 TNS 서비스명으로 해석될 수 있음)
-- sqlplus scott/Pass@word@ORCL

-- 올바른 방법 1: 큰따옴표 사용
-- sqlplus scott/"Pass@word#1"@ORCL

-- 올바른 방법 2: 접속 후 비밀번호를 특수문자 없이 변경
ALTER USER scott IDENTIFIED BY "SimplePass2024";

-- JDBC 접속 문자열에서의 처리 (비밀번호에 @ 포함 시)
-- jdbc:oracle:thin:scott/Pass%40word@//hostname:1521/ORCL
-- %40은 @의 URL 인코딩 값

-- 비밀번호 복잡도 정책 확인
SELECT *
FROM dba_profiles
WHERE resource_name LIKE 'PASSWORD%'
  AND profile = 'DEFAULT'
ORDER BY resource_name;

⑤ DB Link 재생성

-- 현재 DB Link 목록 확인
SELECT db_link, username, host
FROM dba_db_links
WHERE owner = 'SCOTT';

-- 기존 DB Link 삭제
DROP DATABASE LINK remote_db_link;

-- DB Link 재생성 (변경된 비밀번호로)
CREATE DATABASE LINK remote_db_link
  CONNECT TO remote_user IDENTIFIED BY "NewRemotePass#1"
  USING 'REMOTE_DB_TNS';

-- DB Link 접속 테스트
SELECT * FROM dual@remote_db_link;

-- 공용(PUBLIC) DB Link 재생성 (DBA 권한 필요)
CREATE PUBLIC DATABASE LINK pub_remote_link
  CONNECT TO remote_user IDENTIFIED BY "NewRemotePass#1"
  USING 'REMOTE_DB_TNS';

⑥ 사용자 계정 및 권한 전체 점검 쿼리

-- 계정 상태 전체 점검 (DBA 용)
SELECT
    u.username,
    u.account_status,
    u.lock_date,
    u.expiry_date,
    u.profile,
    u.created,
    p.limit AS failed_login_limit
FROM dba_users u
JOIN dba_profiles p ON u.profile = p.profile
WHERE p.resource_name = 'FAILED_LOGIN_ATTEMPTS'
ORDER BY u.username;

-- 최근 접속 실패 이력 확인 (감사 로그 활성화 필요)
SELECT os_username, username, userhost, timestamp, returncode, action_name
FROM dba_audit_trail
WHERE returncode = 1017   -- ORA-01017 에러 코드
  AND timestamp >= SYSDATE - 7
ORDER BY timestamp DESC;

예방 방법

1. 서비스 계정(Application Account) 전용 프로파일 관리

운영 환경에서 애플리케이션이 사용하는 서비스 계정은 일반 사용자 계정과 분리된 전용 프로파일을 적용해야 합니다. 비밀번호 만료와 잠금으로 인한 장애를 방지하기 위해 PASSWORD_LIFE_TIME과 FAILED_LOGIN_ATTEMPTS를 업무 특성에 맞게 설정하고, 변경 이력을 문서화하여 관리하십시오.


sql
--

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

umzzil nng — Thu, 21 May 2026 05:44:31 +0000

ORA-01000란?

ORA-01000 에러는 Oracle 데이터베이스에서 최대 오픈 커서 수를 초과했을 때 발생하는 에러입니다. Oracle은 세션당 동시에 열 수 있는 커서(Cursor)의 수를 OPEN_CURSORS 파라미터로 제한하며, 이 한계치를 넘어서면 더 이상 새로운 커서를 열 수 없게 됩니다. 주로 애플리케이션 코드에서 커서를 열고 닫지 않는 리소스 누수(Resource Leak)가 누적되어 발생하며, 운영 환경에서 갑작스럽게 서비스 장애로 이어질 수 있는 심각한 에러입니다.

주요 발생 원인

커서 미해제 (Cursor Leak)
애플리케이션에서 SQL을 실행한 후 커서를 명시적으로 닫지 않으면, 해당 커서는 세션이 종료될 때까지 계속 오픈 상태로 남아 있습니다. Java, Python 등의 애플리케이션에서 PreparedStatement나 ResultSet을 close() 하지 않는 경우가 대표적이며, 트래픽이 증가할수록 누적 속도가 빨라져 결국 한계에 도달합니다.
반복 루프 내 커서 재사용 미흡
PL/SQL 또는 애플리케이션 코드에서 루프 안에서 매번 새로운 커서를 열고 닫지 않으면 커서가 기하급수적으로 증가합니다. 특히 FOR 루프나 WHILE 루프 내부에서 동적 SQL(EXECUTE IMMEDIATE, OPEN cursor FOR)을 반복 사용할 때 커서 핸들을 제대로 관리하지 않으면 문제가 됩니다.
OPEN_CURSORS 파라미터 값이 너무 낮게 설정됨
기본값 또는 초기 설정값이 실제 애플리케이션의 동시 커서 사용량에 비해 너무 작게 설정된 경우입니다. 개발 환경에서는 문제가 없다가 운영 환경에서 동시 접속자 수가 늘어나면서 갑자기 에러가 발생하는 패턴이 여기에 해당합니다.
Connection Pool 설정 문제
커넥션 풀에서 커넥션을 재사용할 때, 이전 트랜잭션에서 닫히지 않은 커서가 남아 있는 상태로 커넥션이 반환되는 경우입니다. 풀링된 커넥션은 실제 DB 세션을 재사용하기 때문에, 커서 누수가 있는 커넥션이 반환되면 동일 세션에 커서가 계속 쌓이게 됩니다.
PL/SQL 예외 처리 누락
PL/SQL 블록에서 예외가 발생했을 때 EXCEPTION 절에서 커서를 닫지 않으면 커서가 오픈 상태로 방치됩니다. 정상 경로에서는 커서를 닫더라도, 에러 경로에서 CLOSE cursor 구문이 없으면 예외 발생 시마다 커서가 누적됩니다.

해결 방법

1. 현재 오픈 커서 현황 확인

문제 발생 시 가장 먼저 현재 세션별 오픈 커서 현황을 파악해야 합니다.

-- 세션별 오픈 커서 수 확인
SELECT 
    s.sid,
    s.serial#,
    s.username,
    s.machine,
    s.program,
    s.status,
    COUNT(c.cursor_num) AS open_cursor_count
FROM 
    v$session s
JOIN 
    v$open_cursor c ON s.sid = c.sid
WHERE 
    s.username IS NOT NULL
GROUP BY 
    s.sid, s.serial#, s.username, s.machine, s.program, s.status
ORDER BY 
    open_cursor_count DESC;

-- 특정 세션에서 오픈된 커서 SQL 내용 확인
SELECT 
    c.sid,
    c.cursor_num,
    c.sql_text,
    c.last_sql_active_time
FROM 
    v$open_cursor c
WHERE 
    c.sid = &target_sid  -- 커서가 많은 SID 입력
ORDER BY 
    c.last_sql_active_time DESC;

2. OPEN_CURSORS 파라미터 확인 및 조정

-- 현재 OPEN_CURSORS 설정값 확인
SHOW PARAMETER OPEN_CURSORS;

-- 또는
SELECT name, value 
FROM v$parameter 
WHERE name = 'open_cursors';

-- 동적으로 값 변경 (재시작 불필요, 즉시 적용)
ALTER SYSTEM SET OPEN_CURSORS = 1000 SCOPE = BOTH;

-- 인스턴스 재시작 없이 현재 세션에만 적용
ALTER SESSION SET OPEN_CURSORS = 500;

⚠️ 주의: OPEN_CURSORS 값을 무작정 늘리는 것은 임시방편입니다. 근본 원인인 커서 누수를 반드시 함께 해결해야 합니다.

3. PL/SQL에서 커서 올바르게 관리하기

잘못된 예시 (커서 미해제):

-- 잘못된 방법: 예외 발생 시 커서가 닫히지 않음
DECLARE
    CURSOR emp_cursor IS
        SELECT employee_id, salary FROM employees WHERE department_id = 10;
    v_emp_id employees.employee_id%TYPE;
    v_salary  employees.salary%TYPE;
BEGIN
    OPEN emp_cursor;
    LOOP
        FETCH emp_cursor INTO v_emp_id, v_salary;
        EXIT WHEN emp_cursor%NOTFOUND;
        -- 처리 로직
        DBMS_OUTPUT.PUT_LINE('ID: ' || v_emp_id || ', Salary: ' || v_salary);
    END LOOP;
    CLOSE emp_cursor;
    -- 예외 발생 시 CLOSE가 실행되지 않음!
END;
/

올바른 예시 (예외 처리 포함):

-- 올바른 방법: EXCEPTION 절에서도 커서 닫기
DECLARE
    CURSOR emp_cursor IS
        SELECT employee_id, salary FROM employees WHERE department_id = 10;
    v_emp_id employees.employee_id%TYPE;
    v_salary  employees.salary%TYPE;
BEGIN
    OPEN emp_cursor;
    LOOP
        FETCH emp_cursor INTO v_emp_id, v_salary;
        EXIT WHEN emp_cursor%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE('ID: ' || v_emp_id || ', Salary: ' || v_salary);
    END LOOP;
    CLOSE emp_cursor;

EXCEPTION
    WHEN OTHERS THEN
        -- 커서가 열려 있으면 반드시 닫기
        IF emp_cursor%ISOPEN THEN
            CLOSE emp_cursor;
        END IF;
        RAISE; -- 예외 재발생
END;
/

FOR 루프 사용으로 커서 자동 관리 (권장):

-- FOR 루프는 커서를 자동으로 열고 닫아줌 (Best Practice)
BEGIN
    FOR emp_rec IN (
        SELECT employee_id, salary 
        FROM employees 
        WHERE department_id = 10
    ) LOOP
        DBMS_OUTPUT.PUT_LINE(
            'ID: ' || emp_rec.employee_id || 
            ', Salary: ' || emp_rec.salary
        );
    END LOOP;
    -- 루프 종료 시 자동으로 커서 닫힘
END;
/

4. 동적 SQL에서 커서 관리

-- REF CURSOR를 사용한 동적 SQL 커서 관리
DECLARE
    TYPE ref_cursor_type IS REF CURSOR;
    v_cursor    ref_cursor_type;
    v_emp_id    employees.employee_id%TYPE;
    v_dept_id   NUMBER := 10;
    v_sql       VARCHAR2(500);
BEGIN
    v_sql := 'SELECT employee_id FROM employees WHERE department_id = :dept_id';

    OPEN v_cursor FOR v_sql USING v_dept_id;

    LOOP
        FETCH v_cursor INTO v_emp_id;
        EXIT WHEN v_cursor%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE('Employee ID: ' || v_emp_id);
    END LOOP;

    CLOSE v_cursor;  -- 반드시 명시적으로 닫기

EXCEPTION
    WHEN OTHERS THEN
        IF v_cursor%ISOPEN THEN
            CLOSE v_cursor;
        END IF;
        RAISE;
END;
/

5. 문제 세션 강제 종료 (긴급 조치)

-- 커서 누수가 심한 세션 강제 종료 (운영 환경 신중하게 사용)
-- 먼저 대상 SID와 SERIAL# 확인
SELECT sid, serial#, username, machine, program
FROM v$session
WHERE sid = &target_sid;

-- 세션 강제 종료
ALTER SYSTEM KILL SESSION '&sid,&serial#' IMMEDIATE;

예방 방법

1. 애플리케이션 레벨에서 try-with-resources 패턴 사용

Java 기반 애플리케이션이라면 반드시 try-with-resources 구문을 사용하거나 finally 블록에서 Connection, PreparedStatement, ResultSet을 모두 닫아야 합니다. 코드 리뷰 체크리스트에 커서/커넥션 닫힘 여부

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

umzzil nng — Thu, 21 May 2026 05:35:18 +0000

ORA-00979란?

ORA-00979는 Oracle SQL에서 GROUP BY 절을 사용할 때 SELECT 절에 포함된 컬럼이나 표현식이 GROUP BY 절에 명시되지 않았을 때 발생하는 에러입니다. Oracle의 SQL 표준 규칙에 따르면, GROUP BY를 사용하는 쿼리에서 SELECT 절에 포함되는 모든 컬럼은 반드시 집계 함수(SUM, COUNT, MAX, MIN, AVG 등)로 감싸거나 GROUP BY 절에 명시되어야 합니다. 이 에러는 주로 복잡한 집계 쿼리를 작성하거나, 기존 쿼리를 수정하는 과정에서 컬럼을 추가하면서 GROUP BY 절을 업데이트하지 않았을 때 빈번하게 발생합니다.

주요 발생 원인

1. SELECT 절의 컬럼이 GROUP BY 절에 누락된 경우

가장 일반적인 원인으로, SELECT 절에 여러 컬럼을 나열했지만 일부 컬럼을 GROUP BY 절에 포함시키지 않은 경우입니다. 특히 쿼리를 수정하면서 새로운 컬럼을 SELECT에 추가했지만 GROUP BY를 수정하지 않았을 때 자주 발생합니다.

2. 표현식(Expression)이 GROUP BY와 정확히 일치하지 않는 경우

SELECT 절에서 사용한 표현식(예: TRUNC(order_date, 'MM'))이 GROUP BY 절의 표현식과 정확히 동일하지 않은 경우 에러가 발생합니다. Oracle은 표현식의 완전한 일치를 요구하기 때문에 대소문자나 공백까지도 영향을 줄 수 있습니다.

3. 서브쿼리 또는 인라인 뷰 내부의 GROUP BY 누락

메인 쿼리는 올바르게 작성되었더라도, 서브쿼리나 인라인 뷰 내부에서 집계를 처리하는 과정에서 해당 에러가 발생할 수 있습니다. 중첩된 쿼리 구조에서는 각 쿼리 레벨마다 GROUP BY 규칙이 독립적으로 적용됩니다.

4. HAVING 절과 GROUP BY의 컬럼 불일치

HAVING 절에서 참조하는 컬럼이 GROUP BY에 없거나 집계 함수로 감싸지지 않은 경우에도 동일한 에러가 발생할 수 있습니다. HAVING 절은 그룹에 대한 필터링을 수행하므로, GROUP BY에 명시된 컬럼이나 집계 함수만 사용 가능합니다.

5. 조인(JOIN) 후 집계 시 컬럼 관리 실수

여러 테이블을 조인한 뒤 집계 쿼리를 작성할 때, 조인된 테이블의 컬럼을 SELECT에 추가하면서 GROUP BY 절에 해당 컬럼을 빠뜨리는 경우가 많습니다. 조인 결과에는 컬럼 수가 많아지기 때문에 실수가 발생하기 쉽습니다.

해결 방법

해결책 1: 누락된 컬럼을 GROUP BY 절에 추가

가장 기본적인 해결 방법입니다. SELECT 절에 있는 집계 함수가 아닌 모든 컬럼을 GROUP BY에 추가합니다.

-- 에러 발생 쿼리
SELECT department_id, job_id, SUM(salary)
FROM employees
GROUP BY department_id;
-- ORA-00979: not a GROUP BY expression 발생 (job_id 누락)

-- 해결된 쿼리
SELECT department_id, job_id, SUM(salary)
FROM employees
GROUP BY department_id, job_id;

해결책 2: 집계가 불필요한 컬럼은 SELECT에서 제거

해당 컬럼이 분석에 꼭 필요하지 않다면, SELECT에서 제거하는 것도 올바른 해결책입니다.

-- 에러 발생 쿼리
SELECT department_id, employee_name, COUNT(*) AS emp_count
FROM employees
GROUP BY department_id;

-- 해결 방법 1: GROUP BY에 추가
SELECT department_id, employee_name, COUNT(*) AS emp_count
FROM employees
GROUP BY department_id, employee_name;

-- 해결 방법 2: 필요 없는 컬럼 제거
SELECT department_id, COUNT(*) AS emp_count
FROM employees
GROUP BY department_id;

해결책 3: 표현식을 GROUP BY와 정확히 일치시키기

SELECT 절에서 사용하는 표현식과 GROUP BY 절의 표현식이 완전히 동일해야 합니다.

-- 에러 발생 쿼리
SELECT TRUNC(hire_date, 'MM') AS hire_month, COUNT(*) AS cnt
FROM employees
GROUP BY hire_date;
-- ORA-00979: 표현식 불일치

-- 해결된 쿼리: 동일한 표현식 사용
SELECT TRUNC(hire_date, 'MM') AS hire_month, COUNT(*) AS cnt
FROM employees
GROUP BY TRUNC(hire_date, 'MM');

-- 실무 예: 월별 매출 집계
SELECT 
    TO_CHAR(order_date, 'YYYY-MM') AS order_month,
    product_category,
    SUM(sale_amount)               AS total_sales,
    COUNT(*)                       AS order_count
FROM sales
GROUP BY 
    TO_CHAR(order_date, 'YYYY-MM'),
    product_category
ORDER BY order_month, product_category;

해결책 4: 서브쿼리를 활용하여 구조 분리

GROUP BY 대상이 아닌 컬럼이 꼭 필요한 경우, 서브쿼리나 인라인 뷰를 활용하여 집계와 조회를 분리합니다.

-- 에러 발생 쿼리: 부서별 최고 급여자의 이름을 함께 표시하려 할 때
SELECT department_id, first_name, MAX(salary)
FROM employees
GROUP BY department_id;
-- ORA-00979 발생

-- 해결된 쿼리: 서브쿼리와 조인 활용
SELECT 
    e.department_id,
    e.first_name,
    e.salary
FROM employees e
JOIN (
    SELECT department_id, MAX(salary) AS max_salary
    FROM employees
    GROUP BY department_id
) dept_max
ON e.department_id = dept_max.department_id
AND e.salary = dept_max.max_salary
ORDER BY e.department_id;

해결책 5: HAVING 절 수정

HAVING 절에서도 동일한 규칙이 적용됩니다.

-- 에러 발생 쿼리
SELECT department_id, SUM(salary) AS total_sal
FROM employees
GROUP BY department_id
HAVING job_id = 'IT_PROG';
-- ORA-00979: job_id는 GROUP BY에 없고 집계 함수도 아님

-- 해결 방법 1: WHERE 절로 이동 (그룹화 전 필터링)
SELECT department_id, SUM(salary) AS total_sal
FROM employees
WHERE job_id = 'IT_PROG'
GROUP BY department_id;

-- 해결 방법 2: job_id를 GROUP BY에 추가
SELECT department_id, job_id, SUM(salary) AS total_sal
FROM employees
GROUP BY department_id, job_id
HAVING job_id = 'IT_PROG';

해결책 6: 복잡한 조인 쿼리에서의 처리

-- 에러 발생 쿼리: 부서명을 포함한 급여 집계
SELECT 
    d.department_name,
    e.department_id,
    e.job_id,
    SUM(e.salary) AS total_salary
FROM employees e
JOIN departments d ON e.department_id = d.department_id
GROUP BY e.department_id;
-- ORA-00979: department_name, job_id 누락

-- 해결된 쿼리
SELECT 
    d.department_name,
    e.department_id,
    e.job_id,
    SUM(e.salary) AS total_salary
FROM employees e
JOIN departments d ON e.department_id = d.department_id
GROUP BY d.department_name, e.department_id, e.job_id
ORDER BY e.department_id, e.job_id;

예방 방법

1. SELECT 절 작성 후 GROUP BY를 체계적으로 검토하는 습관

쿼리 작성 시 SELECT 절을 먼저 완성한 뒤, 집계 함수가 아닌 모든 컬럼과 표현식을 그대로 복사하여 GROUP BY 절에 붙여넣는 방식을 사용하면 누락을 방지할 수 있습니다. 특히 컬럼 수가 많은 쿼리에서 매우 효과적인 방법입니다.

2. 쿼리 리뷰 시 SELECT-GROUP BY 매핑 테이블 작성

복잡한 집계 쿼리를 작성할 때는 각 컬럼이 집계 함수로 처리되는지, GROUP BY에 포함되는지를 명시적으로 정리하는 습관을 들이세요. 코드 리뷰 시에도 다른 팀원이 쉽게 검토할 수 있어 유지보

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

umzzil nng — Thu, 21 May 2026 04:59:08 +0000

ORA-00942: Table or View Does Not Exist 완벽 해결 가이드

ORA-00942란?

ORA-00942는 SQL 쿼리에서 참조한 테이블 또는 뷰가 현재 세션의 컨텍스트에서 존재하지 않거나 접근할 수 없을 때 발생하는 에러입니다. 단순히 테이블이 없는 경우뿐만 아니라, 테이블은 존재하지만 현재 접속한 사용자에게 해당 객체에 대한 권한이 없을 때도 동일한 에러가 발생한다는 점이 실무에서 혼란을 주는 주요 원인입니다. Oracle은 보안상의 이유로 권한이 없는 객체와 존재하지 않는 객체를 동일한 에러 코드로 처리하기 때문에, 정확한 원인 파악을 위한 추가적인 확인 작업이 필요합니다.

주요 발생 원인

테이블 또는 뷰가 실제로 존재하지 않는 경우
해당 스키마에 테이블이 생성되지 않았거나, 이미 삭제(DROP)된 경우에 발생합니다. 특히 개발/운영 환경 간 스키마 동기화가 제대로 되지 않았을 때 자주 발생하며, 신규 배포 후 DDL 스크립트 누락이 원인인 경우가 많습니다.
다른 스키마의 객체에 대한 권한 미부여
Oracle에서는 다른 스키마 소유의 테이블에 접근하려면 명시적으로 SELECT, INSERT, UPDATE, DELETE 등의 권한이 부여되어 있어야 합니다. 권한이 없으면 테이블이 존재하더라도 Oracle은 마치 존재하지 않는 것처럼 ORA-00942를 반환합니다.
스키마명(오너) 미지정 또는 오타
SCOTT.EMP 처럼 스키마명을 명시해야 하는 상황에서 EMP만 사용하거나, 스키마명이나 테이블명에 오타가 있는 경우 발생합니다. 대소문자 문제도 원인이 될 수 있는데, 큰따옴표로 생성된 객체는 대소문자가 구별되므로 정확히 일치하지 않으면 에러가 발생합니다.
Synonym(동의어)이 없거나 잘못 설정된 경우
Public Synonym 또는 Private Synonym이 존재하지 않거나, Synonym이 가리키는 원본 객체가 삭제된 경우에 발생합니다. 특히 여러 애플리케이션이 Synonym을 통해 테이블에 접근하는 구조에서 원본 테이블이 변경되면 이 문제가 빈번히 발생합니다.
Stored Procedure/Function 내 동적 권한 문제
저장 프로시저나 함수 내에서 참조하는 테이블에 대해 직접 권한(Direct Privilege)이 아닌 롤(Role)을 통해 권한이 부여된 경우, 컴파일 또는 실행 시 ORA-00942가 발생할 수 있습니다. Oracle PL/SQL에서는 Role을 통해 부여된 권한은 기본적으로 Named Block(프로시저, 함수, 패키지)에서 인식되지 않습니다.

해결 방법

✅ 원인 1: 테이블 존재 여부 확인 및 재생성

먼저 해당 테이블이 어느 스키마에 존재하는지 확인합니다.

-- 현재 사용자 소유 테이블 확인
SELECT table_name, owner
FROM user_tables
WHERE table_name = 'EMP';  -- 대문자로 입력

-- DBA 권한이 있는 경우: 전체 스키마에서 확인
SELECT table_name, owner
FROM dba_tables
WHERE table_name = 'EMP';

-- 뷰 확인
SELECT view_name, owner
FROM dba_views
WHERE view_name = 'EMP_VIEW';

-- RECYCLE BIN에서 삭제된 테이블 확인 (Oracle 10g 이상)
SELECT object_name, original_name, droptime
FROM recyclebin
WHERE original_name = 'EMP';

-- RECYCLE BIN에서 복구
FLASHBACK TABLE emp TO BEFORE DROP;

✅ 원인 2: 권한 부여 및 확인

-- 특정 사용자에게 테이블 권한 확인
SELECT grantee, owner, table_name, privilege
FROM dba_tab_privs
WHERE table_name = 'EMP'
  AND grantee = 'APP_USER';

-- 권한 부여 (DBA 또는 테이블 소유자가 실행)
GRANT SELECT ON scott.emp TO app_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON scott.emp TO app_user;

-- 모든 사용자에게 조회 권한 부여
GRANT SELECT ON scott.emp TO PUBLIC;

-- 롤을 통한 권한 부여
CREATE ROLE read_only_role;
GRANT SELECT ON scott.emp TO read_only_role;
GRANT read_only_role TO app_user;

✅ 원인 3: 스키마명 명시 및 대소문자 확인

-- 스키마명을 명시하여 접근
SELECT * FROM scott.emp;

-- 현재 세션의 스키마 확인
SELECT sys_context('USERENV', 'CURRENT_SCHEMA') FROM dual;
SELECT user FROM dual;

-- 대소문자 구별로 생성된 객체 확인
-- 따옴표로 생성된 경우: CREATE TABLE "myTable" (...)
SELECT * FROM "myTable";  -- 반드시 따옴표 사용

-- 일반적인 방식으로 생성된 경우
CREATE TABLE MY_TABLE (id NUMBER);
SELECT * FROM my_table;  -- 대소문자 무관하게 접근 가능
SELECT * FROM MY_TABLE;  -- 동일하게 접근 가능

✅ 원인 4: Synonym 생성 및 확인

-- 현재 접근 가능한 Synonym 확인
SELECT synonym_name, table_owner, table_name
FROM all_synonyms
WHERE synonym_name = 'EMP';

-- Private Synonym 생성
CREATE SYNONYM emp FOR scott.emp;

-- Public Synonym 생성 (DBA 권한 필요)
CREATE PUBLIC SYNONYM emp FOR scott.emp;

-- 깨진 Synonym 확인 및 재생성
SELECT *
FROM all_synonyms s
WHERE NOT EXISTS (
    SELECT 1
    FROM all_objects o
    WHERE o.owner = s.table_owner
      AND o.object_name = s.table_name
);

-- 잘못된 Synonym 삭제 후 재생성
DROP PUBLIC SYNONYM emp;
CREATE PUBLIC SYNONYM emp FOR scott.emp;

✅ 원인 5: PL/SQL에서 롤 권한 문제 해결

-- 문제 상황: 롤을 통해 부여된 권한은 PL/SQL에서 동작 안 함
-- 해결: 직접 권한(Direct Privilege) 부여

-- 잘못된 방법 (Role을 통한 권한 부여 - PL/SQL 내부에서 동작 안 함)
GRANT read_only_role TO app_user;  -- Role로 부여

-- 올바른 방법 (Direct Privilege 부여)
GRANT SELECT ON scott.emp TO app_user;  -- 직접 부여

-- 또는 AUTHID CURRENT_USER를 사용하는 방법
CREATE OR REPLACE PROCEDURE get_emp_info
    AUTHID CURRENT_USER  -- 현재 사용자의 권한으로 실행
AS
BEGIN
    FOR rec IN (SELECT * FROM scott.emp) LOOP
        DBMS_OUTPUT.PUT_LINE(rec.ename);
    END LOOP;
END;
/

-- 권한 부여 후 프로시저 재컴파일
ALTER PROCEDURE get_emp_info COMPILE;

예방 방법

1. 📋 정기적인 스키마 권한 감사(Audit) 수행

배포 전후에 필요한 권한이 올바르게 설정되어 있는지 검증하는 스크립트를 표준화하여 운영하세요. 특히 신규 테이블 추가 시 애플리케이션 계정에 대한 권한 부여를 배포 체크리스트에 포함시키면 누락을 방지할 수 있습니다.

-- 특정 사용자가 접근 가능한 모든 객체 목록 조회
SELECT owner, object_name, object_type, privilege
FROM (
    SELECT t.owner, t.table_name AS object_name, 'TABLE' AS object_type, p.privilege
    FROM dba_tables t
    JOIN dba_tab_privs p ON t.owner = p.owner AND t.table_name = p.table_name
    WHERE p.grantee = 'APP_USER'
    UNION ALL
    SELECT v.owner, v.view_name, 'VIEW', p.privilege
    FROM dba_views v
    JOIN dba_tab_privs p ON v.owner = p.owner AND v.view_name = p.table_name
    WHERE p.grantee = 'APP_USER'
)
ORDER BY owner, object_name;

2. 🔄 개발/스테이징/운영 환경 스키마 동기화 자동화

환경 간 스키마 불일치는 ORA-00942의 매우 흔한 원인입니다. DDL 변경 사항을 형상관리(Git 등)로 관리하고, 마이그레이션 스크립트를 자동으로 적용하는 파이프라인을 구축하는 것이 좋습

DEV Community: umzzil nng

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

ORA-00064: object is too large to allocate on this OS

ORA-00064란?

주요 발생 원인

해결 방법

원인 1 해결: OS 커널 공유 메모리 파라미터 조정 (Linux 기준)

원인 2 해결: AMM 환경에서 /dev/shm 크기 조정 및 파라미터 수정

원인 3 해결: processes 파라미터 적정값 조정

예방 방법

관련 에러

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

ORA-00060란?

주요 발생 원인

해결 방법

원인 1 해결: 트랜잭션 잠금 순서 통일

원인 2 해결: 외래 키 인덱스 생성

원인 3 해결: 비트맵 인덱스 환경에서의 동시성 제어

데드락 발생 시 애플리케이션 재시도 로직 (PL/SQL)

예방 방법

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

ORA-00057란?

주요 발생 원인

해결 방법

원인 1 해결 - 현재 임시 테이블 잠금 현황 파악 및 세션 정리

원인 2 해결 - 미종료 트랜잭션 탐지 및 정리

원인 3 해결 - GTT 사용 현황 감사 및 재설계

예방 방법

관련 에러

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

ORA-00055: maximum number of DML locks exceeded 완벽 해결 가이드

ORA-00055란?

주요 발생 원인

1. DML_LOCKS 파라미터 값이 너무 낮게 설정된 경우

2. 하나의 트랜잭션에서 지나치게 많은 테이블에 DML을 수행하는 경우

3. 트랜잭션이 COMMIT/ROLLBACK 없이 장시간 열려 있는 경우

해결 방법

해결 1: 현재 DML_LOCKS 상태 확인 및 파라미터 증가

해결 2: 장시간 열린 트랜잭션(미종료 세션) 탐지 및 정리

해결 3: 대형 배치 트랜잭션을 분할하여 처리

해결 4: 트리거 내부의 불필요한 DML 최소화

예방 방법

1. DML_LOCKS 파라미터 정기 모니터링 및 용량 계획 수립

2. 애플리케이션 트랜잭션 설계 원칙 수립 및 코드 리뷰 프로세스 강화

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

ORA-00054 완벽 가이드: resource busy and acquire with NOWAIT specified

ORA-00054란?

주요 발생 원인

해결 방법

1단계: 현재 잠금 보유 세션 파악

2단계: 블로킹 세션과 대기 세션 연관 조회

3단계: 해당 세션이 실행 중인 SQL 확인

4단계: 세션 종료 또는 COMMIT/ROLLBACK 유도

5단계: NOWAIT 대신 WAIT 옵션 사용

6단계: DDL 작업 전 잠금 세션 점검 스크립트

7단계: 인덱스/DDL ONLINE 옵션 활용

예방 방법

1. 트랜잭션 관리 정책 수립 및 자동 COMMIT 방지

2. DDL 작업 전 표준 점검 절차(SOP) 수립

3. 애플리케이션 레벨 에러 처리 및 재시도 로직 구현

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

ORA-00051: timeout occurred while waiting for a resource

ORA-00051란?

주요 발생 원인

해결 방법

1. DISTRIBUTED_LOCK_TIMEOUT 파라미터 확인 및 조정

2. 현재 락 대기 세션 및 블로킹 세션 확인

3. 블로킹 세션 강제 종료

4. In-doubt 트랜잭션 확인 및 처리

5. DB Link 연결 상태 확인

6. 장기 미완료 트랜잭션 확인

예방 방법

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

ORA-01031란?

주요 발생 원인

해결 방법

원인 1: 객체 권한 직접 부여

원인 2: PL/SQL 내 롤 권한 문제 해결

원인 3: 시스템 권한 부여

원인 4: SYSDBA 접속 문제 해결