오라클 프로시저 정보 조회 완전 가이드 - 실무에서 바로 쓰는 7가지 쿼리

오라클 데이터베이스에서 프로시저(Stored Procedure) 정보를 조회할 때, 데이터 딕셔너리 뷰를 활용하면 정확하고 체계적으로 필요한 정보를 얻을 수 있습니다. 이 글에서는 실무에서 자주 사용하는 7가지 핵심 쿼리를 목적별로 정리했습니다.

1. 프로시저 기본 정보 조회 (필수)

가장 먼저 확인해야 할 프로시저의 기본 정보입니다.

SELECT
    OWNER,
    OBJECT_NAME,
    OBJECT_TYPE,
    STATUS,
    CREATED,
    LAST_DDL_TIME
FROM ALL_OBJECTS
WHERE OBJECT_TYPE = 'PROCEDURE'
  AND OBJECT_NAME = UPPER('프로시저명');

주요 컬럼 설명:

• OWNER: 프로시저 소유자
• STATUS: VALID(정상) 또는 INVALID(오류)
• CREATED: 최초 생성일
• LAST_DDL_TIME: 마지막 수정일

팁: 본인 스키마의 프로시저만 조회하려면 ALL_OBJECTS 대신 USER_OBJECTS를 사용하세요.

2. 프로시저 파라미터 정보 조회

프로시저의 입력/출력 파라미터 구조를 파악할 때 사용합니다.

SELECT
    ARGUMENT_NAME,
    POSITION,
    IN_OUT,
    DATA_TYPE,
    DATA_LENGTH,
    DATA_PRECISION,
    DATA_SCALE
FROM ALL_ARGUMENTS
WHERE OBJECT_NAME = UPPER('프로시저명')
ORDER BY POSITION;

주요 컬럼 설명:

• IN_OUT: IN, OUT, IN OUT 구분
• POSITION: 파라미터 순서 (0부터 시작)
• ARGUMENT_NAME이 NULL인 경우: RETURN 값 또는 첫 번째 파라미터

패키지 내 프로시저 조회 시:

AND PACKAGE_NAME = '패키지명'

3. 프로시저 소스 코드 조회

프로시저의 전체 로직을 확인할 때 사용합니다.

SELECT
    LINE,
    TEXT
FROM ALL_SOURCE
WHERE NAME = UPPER('프로시저명')
  AND TYPE = 'PROCEDURE'
ORDER BY LINE;

소스 코드가 라인별로 정렬되어 출력되므로, 로직을 단계별로 분석하기 좋습니다.

권장: 본인 스키마만 조회할 때는 USER_SOURCE를 사용하면 더 빠릅니다.

4. 특정 테이블을 참조하는 프로시저 찾기

테이블 구조 변경 전 영향도 분석에 필수적인 쿼리입니다.

SELECT DISTINCT
    NAME AS PROCEDURE_NAME
FROM ALL_SOURCE
WHERE TYPE = 'PROCEDURE'
  AND UPPER(TEXT) LIKE '%테이블명%';

실무 활용 예시:

• 테이블 컬럼 변경 전 영향받는 프로시저 파악
• 테이블 삭제 전 의존성 체크
• 리팩토링 범위 결정

5. INVALID 상태 프로시저 조회

컴파일 오류가 발생한 프로시저를 한눈에 확인합니다.

SELECT
    OBJECT_NAME,
    STATUS,
    LAST_DDL_TIME
FROM USER_OBJECTS
WHERE OBJECT_TYPE = 'PROCEDURE'
  AND STATUS = 'INVALID';

배포 후 또는 테이블 구조 변경 후 반드시 확인해야 할 쿼리입니다.

6. 프로시저 컴파일 에러 상세 확인

INVALID 상태인 프로시저의 정확한 오류 원인을 파악합니다.

SELECT
    LINE,
    POSITION,
    TEXT
FROM USER_ERRORS
WHERE NAME = UPPER('프로시저명')
ORDER BY SEQUENCE;

장점:

• Eclipse, DBeaver 등 IDE의 오류 메시지와 1:1 매칭
• 라인 번호와 포지션으로 정확한 오류 위치 파악
• 운영 장애 발생 시 빠른 원인 분석 가능

7. 패키지 내 프로시저 목록 조회

패키지에 포함된 모든 프로시저를 확인합니다.

SELECT
    OBJECT_NAME,
    PROCEDURE_NAME
FROM ALL_PROCEDURES
WHERE OBJECT_TYPE = 'PACKAGE'
  AND OBJECT_NAME = '패키지명';

대규모 시스템에서 패키지 구조를 파악할 때 유용합니다.

실무 활용 시나리오별 쿼리 조합

목적	사용 쿼리
프로시저 존재 여부 확인	ALL_OBJECTS
호출 방법 확인 (파라미터)	ALL_ARGUMENTS
로직 분석 및 수정	ALL_SOURCE
컴파일 오류 해결	USER_ERRORS
테이블 변경 영향도 분석	ALL_SOURCE (LIKE)

추가 팁

1. 권한 문제 해결

• ALL_* 뷰: 접근 가능한 모든 객체
• USER_* 뷰: 본인 스키마만 (더 빠름)
• DBA_* 뷰: DBA 권한 필요

2. 성능 최적화

• OBJECT_NAME 조건에 항상 UPPER() 함수 사용
• 본인 스키마만 조회할 때는 USER_* 뷰 우선 사용
• LIKE 검색은 필요한 경우에만 사용

3. 자동화 스크립트

-- 전체 INVALID 프로시저 재컴파일
SELECT 'ALTER PROCEDURE ' || OBJECT_NAME || ' COMPILE;'
FROM USER_OBJECTS
WHERE OBJECT_TYPE = 'PROCEDURE'
  AND STATUS = 'INVALID';

보너스: 통합 조회 쿼리

프로시저의 모든 정보를 한 번에 확인하고 싶을 때 사용하는 통합 쿼리입니다.

-- 프로시저 상세 정보 (기본 정보 + 파라미터)
SELECT 
    o.OBJECT_NAME,
    o.STATUS,
    o.CREATED,
    o.LAST_DDL_TIME,
    a.ARGUMENT_NAME,
    a.POSITION,
    a.IN_OUT,
    a.DATA_TYPE
FROM USER_OBJECTS o
LEFT JOIN ALL_ARGUMENTS a 
    ON o.OBJECT_NAME = a.OBJECT_NAME
WHERE o.OBJECT_TYPE = 'PROCEDURE'
  AND o.OBJECT_NAME = UPPER('프로시저명')
ORDER BY a.POSITION;

엑셀 추출용 쿼리

문서화나 보고서 작성을 위해 엑셀로 추출하기 좋은 형태의 쿼리입니다.

-- 전체 프로시저 목록 (엑셀 추출용)
SELECT 
    ROWNUM AS NO,
    OBJECT_NAME AS "프로시저명",
    STATUS AS "상태",
    TO_CHAR(CREATED, 'YYYY-MM-DD') AS "생성일",
    TO_CHAR(LAST_DDL_TIME, 'YYYY-MM-DD HH24:MI:SS') AS "최종수정일",
    CASE 
        WHEN STATUS = 'VALID' THEN '정상'
        ELSE '오류'
    END AS "비고"
FROM USER_OBJECTS
WHERE OBJECT_TYPE = 'PROCEDURE'
ORDER BY OBJECT_NAME;

특정 스키마의 모든 프로시저 조회

다른 스키마의 프로시저를 조회할 때 (권한 필요):

SELECT
    OWNER AS "스키마",
    OBJECT_NAME AS "프로시저명",
    STATUS AS "상태",
    CREATED AS "생성일"
FROM ALL_OBJECTS
WHERE OBJECT_TYPE = 'PROCEDURE'
  AND OWNER = 'HR'  -- 특정 스키마명
ORDER BY OBJECT_NAME;

프로시저 의존성 조회

프로시저가 참조하는 객체들을 확인합니다.

SELECT
    NAME AS "프로시저명",
    TYPE AS "타입",
    REFERENCED_OWNER AS "참조스키마",
    REFERENCED_NAME AS "참조객체",
    REFERENCED_TYPE AS "참조타입"
FROM USER_DEPENDENCIES
WHERE NAME = UPPER('프로시저명')
  AND TYPE = 'PROCEDURE'
ORDER BY REFERENCED_TYPE, REFERENCED_NAME;

실행 통계 조회 (성능 분석)

프로시저의 실행 통계를 확인합니다 (10g 이상):

SELECT 
    object_name,
    executions AS "실행횟수",
    ROUND(elapsed_time/1000000, 2) AS "총소요시간(초)",
    ROUND(elapsed_time/executions/1000000, 4) AS "평균소요시간(초)"
FROM v$sql
WHERE sql_text LIKE '%프로시저명%'
  AND command_type = 47  -- PL/SQL EXECUTE
ORDER BY executions DESC;

마치며

오라클의 데이터 딕셔너리 뷰는 프로시저 관리에 있어 강력한 도구입니다. 이 가이드의 쿼리들을 활용하면:

• ✅ 프로시저 정보를 빠르게 파악할 수 있습니다
• ✅ 컴파일 오류를 신속하게 해결할 수 있습니다
• ✅ 테이블 변경의 영향도를 정확히 분석할 수 있습니다
• ✅ 성능 문제를 체계적으로 진단할 수 있습니다

일상적인 개발과 운영 업무에서 이 가이드를 참고하여 효율적으로 작업하시기 바랍니다.

---

태그: Oracle Stored Procedure PL/SQL Database 데이터베이스 프로시저 오라클

난이도: 초급~중급

예상 소요 시간: 15분

---

관련 글 추천

• 오라클 함수(Function) 정보 조회 가이드
• 오라클 트리거(Trigger) 관리 완벽 가이드
• 오라클 패키지(Package) 개발 Best Practice
• 데이터 딕셔너리 뷰 활용 완전 정복
• PL/SQL 성능 튜닝 실전 가이드