💻 TIL - Oracle에서 프로시저 삭제가 안될 때 해결 방법

 

🗓️ 2025-04-16

오늘은 정말 단순하게 DROP PROCEDURE 한 줄 치면 끝날 줄 알았던 일이, 예상치 못하게 오래 걸려서 고생한 케이스를 정리해본다.
결론부터 말하면 세션 락이 원인이었다.

---

🧨 문제 상황

• Oracle에서 기존 프로시저를 삭제하려고 DROP PROCEDURE ㅁㅁㅁㅁㅁ; 실행했는데,
• 쿼리는 돌고 있는데 반응이 없음.
• 무한 대기 상태... 심지어 에러도 안 뜸.

---

🔍 원인 분석

dba_ddl_locks로 확인해보니:

SELECT *
FROM dba_ddl_locks
WHERE name = 'ㅁㅁㅁㅁㅁㅁㅁㅁㅁ';

결과:

SESSION_ID | OWNER        | NAME              | TYPE                | MODE_HELD | MODE_REQUESTED
-----------+--------------+-------------------+---------------------+-----------+----------------
1578       | ㅁㅁㅁㅁㅁㅁㅁ| ㅁㅁㅁㅁㅁㅁㅁㅁㅁ| Table/Procedure/Type| Null      | None
488        | ㅁㅁㅁㅁㅁㅁㅁ| ㅁㅁㅁㅁㅁㅁㅁㅁㅁ| Table/Procedure/Type| Null      | None
...        | ...          | ...               | ...                 | ...       | ...

• 여러 세션이 MODE_HELD = Null, MODE_REQUESTED = None 상태로 락을 걸고 있지도 않는데도 목록에 잡혀 있었다.
• 심지어 세션들이 좀비처럼 살아 있어서 DROP 명령이 락 대기 상태로 대기 중이었다.

---

🛠️ 해결 방법

1. 락 건 세션 목록 조회

SELECT sid, serial#
FROM v$session
WHERE sid IN (1578, 488, 3764, 1817, 3758, 6, 1580);

2. 세션 강제 종료

ALTER SYSTEM KILL SESSION 'SID,SERIAL#' IMMEDIATE;

예시:

ALTER SYSTEM KILL SESSION '1578,12345' IMMEDIATE;

IMMEDIATE 안 붙이면 안 죽는다. 꼭 붙여야 한다.

---

✅ 그리고 다시 DROP 시도

DROP PROCEDURE ㅁㅁㅁㅁㅁㅁㅁㅁㅁ;

✨ 이번엔 바로 삭제 성공!

---

💡 배운 점

• Oracle은 프로시저도 DDL 객체라서 dba_ddl_locks에 잡힐 수 있다.
• 세션이 "현재 사용 중"이 아니더라도, 가비지 상태로 남아있으면 DDL 락이 걸려서 DROP이 안 될 수 있다.
• 이런 경우 v$session에서 세션 ID로 조회 → KILL SESSION으로 수동 정리 필요.
• 진짜 안 될 땐… 이름 바꿔서 새로 만들고 기존 건 나중에 DBA한테 지워달라고 하는 것도 방법이다 😂

---

📝 오늘도 하나 배웠다.
Oracle이 심플한 듯 심플하지 않은 이유, 바로 이런 구석구석에서 발견된다.