오라클 프로시저 상태 조회

Oracle 데이터베이스에서 프로시저 상태를 조회하려면, ALL_OBJECTS, DBA_OBJECTS, 또는 USER_OBJECTS 뷰를 사용할 수 있습니다. 이 뷰들은 프로시저의 상태(VALID/INVALID)와 컴파일 여부 등을 제공합니다.

---

프로시저 상태 조회 쿼리

1. 특정 스키마의 프로시저 상태 조회

SELECT OBJECT_NAME, STATUS, LAST_DDL_TIME
FROM ALL_OBJECTS
WHERE OBJECT_TYPE = 'PROCEDURE'
  AND OWNER = '스키마명'; -- 조회하려는 스키마명

2. 현재 사용자(스키마)의 프로시저 상태 조회

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

  • 컬럼 설명
    - OBJECT_NAME : 프로시저 이름
    - STATUS : VALID(유효), INVALID(유효하지 않음)
    - LAST_DDL_TIME : 마지막 DDL 작업 시간

3. 모든 프로시저 상태 조회 (DBA 권한 필요)

SELECT OWNER, OBJECT_NAME, STATUS, LAST_DDL_TIME
FROM DBA_OBJECTS
WHERE OBJECT_TYPE = 'PROCEDURE';

4. INVALID 상태인 프로시저 조회
 - INVALID 상태인 프로시저는 컴파일되지 않았거나, 참조 객체의 변경으로 인해 무효화된 상태입니다.

SELECT OWNER, OBJECT_NAME, STATUS
FROM ALL_OBJECTS
WHERE OBJECT_TYPE = 'PROCEDURE'
  AND STATUS = 'INVALID';

5. 프로시저 정의 확인
  - 특정 프로시저의 정의를 확인하려면 DBA_SOURCE, ALL_SOURCE, 또는 USER_SOURCE 뷰를 사용할 수 있습니다.

SELECT TEXT
FROM USER_SOURCE
WHERE NAME = '프로시저명'
  AND TYPE = 'PROCEDURE'
ORDER BY LINE;

  - 특정 스키마의 프로시저 소스 코드 확인

SELECT TEXT
FROM ALL_SOURCE
WHERE NAME = '프로시저명'
  AND OWNER = '스키마명'
  AND TYPE = 'PROCEDURE'
ORDER BY LINE;

6. 프로시저의 종속성 확인
  - 프로시저가 참조하는 객체(테이블, 뷰 등)의 상태를 확인하려면 USER_DEPENDENCIES 또는 ALL_DEPENDENCIES 뷰를 사용할 수 있습니다.

SELECT NAME, REFERENCED_NAME, REFERENCED_TYPE, REFERENCED_OWNER
FROM USER_DEPENDENCIES
WHERE NAME = '프로시저명';

7. 프로시저의 마지막 컴파일 시간 확인

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

 

8. INVALID 프로시저 재컴파일
  - INVALID 상태의 프로시저를 재컴파일하려면 다음 명령을 사용합니다
  - 단일 프로시저 컴파일

ALTER PROCEDURE 프로시저명 COMPILE;

  - 모든 INVALID 객체 컴파일

BEGIN
   FOR obj IN (SELECT OBJECT_NAME, OBJECT_TYPE
               FROM USER_OBJECTS
               WHERE STATUS = 'INVALID') LOOP
      EXECUTE IMMEDIATE 'ALTER ' || obj.OBJECT_TYPE || ' ' || obj.OBJECT_NAME || ' COMPILE';
   END LOOP;
END;
/