[Oracle] 테이블명 변경 시 오브젝트 타입별 상태 영향도 파악(VALID → INVALID / 다시 VALID 되는 조건)

728x90

0. 전제

접속: 실습용 계정(예: TEST)으로 로그인
테스트 DB 버전 : 19c
A, B, C 테이블이 있는데,
- 1) A → C로 변경시 A 테이블에 의존성을 가진 객체들의 영향도를 판단
- 2) B → A로 대체했을 때의 영향도를 판단

1. 기본 테이블 + 샘플 데이터 생성

-- 1) 실험용 테이블 생성
CREATE TABLE rename_demo_emp (
    emp_id   NUMBER PRIMARY KEY,
    emp_name VARCHAR2(100)
);

CREATE TABLE rename_demo_emp_tmp (
    emp_id   NUMBER PRIMARY KEY,
    emp_name VARCHAR2(100)
);

INSERT INTO rename_demo_emp VALUES (1, 'SCOTT');
INSERT INTO rename_demo_emp_tmp VALUES (2, 'KING');
INSERT INTO rename_demo_emp_tmp VALUES (3, 'KIM');
COMMIT;

select * from rename_demo_emp;

    EMP_ID EMP_NAME
---------- ----------------------------------------------------------------------------------------------------
         1 SCOTT

select * from rename_demo_emp_tmp;

    EMP_ID EMP_NAME
---------- ----------------------------------------------------------------------------------------------------
         2 KING
         3 KIM

2. 의존 오브젝트들 생성

--1. 뷰(View)
CREATE OR REPLACE VIEW rename_demo_v_emp AS
SELECT emp_id, emp_name
FROM rename_demo_emp;

--2. 프로시저(Static SQL)
CREATE OR REPLACE PROCEDURE rename_demo_p_emp_cnt IS
    l_cnt NUMBER;
BEGIN
    SELECT COUNT(*) INTO l_cnt
    FROM rename_demo_emp;

    DBMS_OUTPUT.PUT_LINE('EMP COUNT = ' || l_cnt);
END;
/

--3. 함수(Function)
CREATE OR REPLACE FUNCTION rename_demo_f_get_name(p_emp_id IN NUMBER)
RETURN VARCHAR2
IS
    l_name VARCHAR2(100);
BEGIN
    SELECT emp_name INTO l_name
    FROM rename_demo_emp
    WHERE emp_id = p_emp_id;

    RETURN l_name;
END;
/

--4. 패키지 (Spec + Body)
CREATE OR REPLACE PACKAGE rename_demo_pkg AS
    PROCEDURE print_all;
END rename_demo_pkg;
/

CREATE OR REPLACE PACKAGE BODY rename_demo_pkg AS
    PROCEDURE print_all IS
    BEGIN
        FOR r IN (SELECT emp_id, emp_name FROM rename_demo_emp ORDER BY emp_id) LOOP
            DBMS_OUTPUT.PUT_LINE(r.emp_id || ' : ' || r.emp_name);
        END LOOP;
    END;
END rename_demo_pkg;
/

--5. 트리거(Trigger)
CREATE OR REPLACE TRIGGER rename_demo_trg_bi_emp
BEFORE INSERT ON rename_demo_emp
FOR EACH ROW
BEGIN
    IF :NEW.emp_id IS NULL THEN
        SELECT NVL(MAX(emp_id), 0) + 1
          INTO :NEW.emp_id
          FROM rename_demo_emp;
    END IF;
END;
/

--6. Materialized View (옵션)
CREATE MATERIALIZED VIEW rename_demo_mv_emp
BUILD IMMEDIATE
REFRESH ON DEMAND
AS
SELECT emp_id, emp_name
FROM rename_demo_emp;

--7. Synonym (옵션: 동작 확인용)
CREATE SYNONYM rename_demo_emp_syn FOR rename_demo_emp;

--8. 동적 SQL 사용하는 프로시저 (의존성 안 잡히는 케이스)
CREATE OR REPLACE PROCEDURE rename_demo_p_dynamic IS
    l_cnt NUMBER;
BEGIN
    EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM rename_demo_emp'
    INTO l_cnt;

    DBMS_OUTPUT.PUT_LINE('DYNAMIC COUNT = ' || l_cnt);
END;
/

3. 현재 오브젝트 상태 확인

SELECT object_name, object_type, status
FROM   user_objects
WHERE  object_name LIKE 'RENAME_DEMO_%'
ORDER BY object_type, object_name;

OBJECT_NAME                                        OBJECT_TYPE                    STATUS
-------------------------------------------------- ------------------------------ -------
RENAME_DEMO_F_GET_NAME                             FUNCTION                       VALID
RENAME_DEMO_MV_EMP                                 MATERIALIZED VIEW              VALID
RENAME_DEMO_PKG                                    PACKAGE                        VALID
RENAME_DEMO_PKG                                    PACKAGE BODY                   VALID
RENAME_DEMO_P_DYNAMIC                              PROCEDURE                      VALID
RENAME_DEMO_P_EMP_CNT                              PROCEDURE                      VALID
RENAME_DEMO_EMP_SYN                                SYNONYM                        VALID
RENAME_DEMO_EMP                                    TABLE                          VALID
RENAME_DEMO_EMP_TMP                                TABLE                          VALID
RENAME_DEMO_MV_EMP                                 TABLE                          VALID
RENAME_DEMO_TRG_BI_EMP                             TRIGGER                        VALID
RENAME_DEMO_V_EMP                                  VIEW                           VALID

12 rows selected.

VIEW / PROCEDURE / FUNCTION / PACKAGE / PACKAGE BODY / TRIGGER / MATERIALIZED VIEW 모두 STATUS = VALID

4. 테이블명 변경 (A → C)

ALTER TABLE rename_demo_emp RENAME TO rename_demo_emp_ren;

이제 다시 상태를 확인

SELECT object_name, object_type, status
FROM   user_objects
WHERE  object_name LIKE 'RENAME_DEMO_%'
ORDER BY object_type, object_name;

OBJECT_NAME                                        OBJECT_TYPE                    STATUS
-------------------------------------------------- ------------------------------ -------
RENAME_DEMO_F_GET_NAME                             FUNCTION                       INVALID
RENAME_DEMO_MV_EMP                                 MATERIALIZED VIEW              INVALID
RENAME_DEMO_PKG                                    PACKAGE                        VALID
RENAME_DEMO_PKG                                    PACKAGE BODY                   INVALID
RENAME_DEMO_P_DYNAMIC                              PROCEDURE                      VALID
RENAME_DEMO_P_EMP_CNT                              PROCEDURE                      INVALID
RENAME_DEMO_EMP_SYN                                SYNONYM                        INVALID
RENAME_DEMO_EMP_REN                                TABLE                          VALID
RENAME_DEMO_EMP_TMP                                TABLE                          VALID
RENAME_DEMO_MV_EMP                                 TABLE                          VALID
RENAME_DEMO_TRG_BI_EMP                             TRIGGER                        INVALID
RENAME_DEMO_V_EMP                                  VIEW                           INVALID

12 rows selected.

RENAME_DEMO_V_EMP → INVALID
RENAME_DEMO_P_EMP_CNT → INVALID
RENAME_DEMO_F_GET_NAME → INVALID
RENAME_DEMO_PKG / RENAME_DEMO_PKG BODY → INVALID
RENAME_DEMO_TRG_BI_EMP → INVALID
RENAME_DEMO_MV_EMP → INVALID
RENAME_DEMO_EMP_SYN → INVALID
RENAME_DEMO_P_DYNAMIC → VALID
- 동적 SQL이라 의존성이 추적 안 돼서, 상태는 VALID 그대로인 게 포인트

테스트

-- 정적 프로시저 실행: INVALID라서 컴파일 시 실패
SQL> SET SERVEROUTPUT ON
SQL> EXEC rename_demo_p_emp_cnt;

ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00905: object TEST.RENAME_DEMO_P_EMP_CNT is invalid
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

-- 동적 SQL 프로시저 실행: ORA-00942
SQL> EXEC rename_demo_p_dynamic;

ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at "TEST.RENAME_DEMO_P_DYNAMIC", line 4
ORA-06512: at line 1

5. 테이블명 변경 (B → A)

ALTER TABLE rename_demo_emp_tmp RENAME TO rename_demo_emp;

select * from rename_demo_emp;

    EMP_ID EMP_NAME
---------- ----------------------------------------------------------------------------------------------------
         2 KING
         3 KIM

다시 상태 확인

SELECT object_name, object_type, status
FROM   user_objects
WHERE  object_name LIKE 'RENAME_DEMO_%'
ORDER BY object_type, object_name;

OBJECT_NAME                                        OBJECT_TYPE                    STATUS
-------------------------------------------------- ------------------------------ -------
RENAME_DEMO_F_GET_NAME                             FUNCTION                       INVALID
RENAME_DEMO_MV_EMP                                 MATERIALIZED VIEW              INVALID
RENAME_DEMO_PKG                                    PACKAGE                        VALID
RENAME_DEMO_PKG                                    PACKAGE BODY                   INVALID
RENAME_DEMO_P_DYNAMIC                              PROCEDURE                      VALID
RENAME_DEMO_P_EMP_CNT                              PROCEDURE                      INVALID
RENAME_DEMO_EMP_SYN                                SYNONYM                        INVALID
RENAME_DEMO_EMP                                    TABLE                          VALID
RENAME_DEMO_EMP_REN                                TABLE                          VALID
RENAME_DEMO_MV_EMP                                 TABLE                          VALID
RENAME_DEMO_TRG_BI_EMP                             TRIGGER                        INVALID
RENAME_DEMO_V_EMP                                  VIEW                           INVALID

12 rows selected.

다른 테이블로 대체했지만,
이미 INVALID로 떨어진 오브젝트들은 여전히 INVALID 상태
뷰/프로시저/함수/패키지/트리거/MV 모두 그대로 INVALID

6. 객체 단위 재컴파일 실험

6-1. procedure 재컴파일

-- 1) 실행 (자동 재컴파일 시도)
SET SERVEROUTPUT ON
EXEC rename_demo_p_emp_cnt;

-- 2) 실행 후 상태 확인
SELECT object_name, object_type, status
FROM   user_objects
WHERE  object_name = 'RENAME_DEMO_P_EMP_CNT';

OBJECT_NAME                                        OBJECT_TYPE                    STATUS
-------------------------------------------------- ------------------------------ -------
RENAME_DEMO_P_EMP_CNT                              PROCEDURE                      VALID

수동 재컴파일 방법

ALTER PROCEDURE rename_demo_p_emp_cnt COMPILE;

6-2. function 재컴파일

-- 1) 함수 호출 (SELECT 문에서 사용)
SELECT rename_demo_f_get_name(1) AS emp_name
FROM   dual;

-- 2) 실행 후 상태 확인
SELECT object_name, object_type, status
FROM   user_objects
WHERE  object_name = 'RENAME_DEMO_F_GET_NAME';

OBJECT_NAME                                        OBJECT_TYPE                    STATUS
-------------------------------------------------- ------------------------------ -------
RENAME_DEMO_F_GET_NAME                             FUNCTION                       VALID

수동 재컴파일 방법

ALTER FUNCTION rename_demo_f_get_name COMPILE;

6-3. PACKAGE 재컴파일

-- 1) 패키지 내 프로시저 호출
SET SERVEROUTPUT ON
EXEC rename_demo_pkg.print_all;

-- 2) 실행 후 상태 확인
SELECT object_name, object_type, status
FROM   user_objects
WHERE  object_name IN ('RENAME_DEMO_PKG', 'RENAME_DEMO_PKG');

OBJECT_NAME                                        OBJECT_TYPE                    STATUS
-------------------------------------------------- ------------------------------ -------
RENAME_DEMO_PKG                                    PACKAGE                        VALID
RENAME_DEMO_PKG                                    PACKAGE BODY                   VALID

수동 재컴파일 방법

-- 패키지 SPEC
ALTER PACKAGE rename_demo_pkg COMPILE SPECIFICATION;

-- 패키지 BODY
ALTER PACKAGE rename_demo_pkg COMPILE BODY;

-- 둘 다 한 번에 (보통 이걸 많이 씀)
ALTER PACKAGE rename_demo_pkg COMPILE;

6-4. TRIGGER 재컴파일

-- 1) 트리거가 걸려 있는 테이블에 INSERT 실행
INSERT INTO rename_demo_emp (emp_id, emp_name)
VALUES (3, 'ADAMS');
COMMIT;

-- 2) 다시 트리거 상태 확인
SELECT object_name, object_type, status
FROM   user_objects
WHERE  object_name = 'RENAME_DEMO_TRG_BI_EMP';

OBJECT_NAME                                        OBJECT_TYPE                    STATUS
-------------------------------------------------- ------------------------------ -------
RENAME_DEMO_TRG_BI_EMP                             TRIGGER                        VALID

수동 재컴파일 방법

ALTER TRIGGER rename_demo_trg_bi_emp COMPILE;

6-5. MATERIALIZED VIEW LOG 재컴파일

MV는 SELECT 하면 자동 컴파일되는 개념이 아님
REFRESH 시점에서 내부적인 재컴파일은 가능하지만 제한적

-- 1) MV REFRESH 시도
BEGIN
    DBMS_MVIEW.REFRESH('RENAME_DEMO_MV_EMP');
END;
/

-- 2) 다시 상태 확인
SELECT object_name, object_type, status
FROM   user_objects
WHERE  object_name = 'RENAME_DEMO_MV_EMP';

OBJECT_NAME                                        OBJECT_TYPE                    STATUS
-------------------------------------------------- ------------------------------ -------
RENAME_DEMO_MV_EMP                                 TABLE                          VALID
RENAME_DEMO_MV_EMP                                 MATERIALIZED VIEW              VALID

수동 재컴파일 방법

ALTER MATERIALIZED VIEW rename_demo_mv_emp COMPILE;

-- 필요시 수동 리프레시 수행
EXEC DBMS_MVIEW.REFRESH('RENAME_DEMO_MV_EMP');

6-6. View 재컴파일

-- 1) VIEW 조회 (정상 조회되는지 먼저 확인)
SELECT *
FROM   rename_demo_v_emp;

    EMP_ID EMP_NAME
---------- ------------------------------
         1 SCOTT
         2 KING
         3 ADAMS

-- 2) 다시 VIEW 상태 확인
SELECT object_name, object_type, status
FROM   user_objects
WHERE  object_name = 'RENAME_DEMO_V_EMP';

OBJECT_NAME                                        OBJECT_TYPE                    STATUS
-------------------------------------------------- ------------------------------ -------
RENAME_DEMO_V_EMP                                  VIEW                           VALID

수동 재컴파일 방법

ALTER VIEW rename_demo_v_emp COMPILE;

6-7. SYNONYM

Synonym은 COMPILE 개념이 없음.

대상 객체만 바르게 존재하면 항상 VALID 상태로 취급됨.

만약 테이블명을 바꿔서 Synonym이 깨졌다면 → 다시 CREATE OR REPLACE

CREATE OR REPLACE SYNONYM rename_demo_emp_syn
  FOR rename_demo_emp;

6-8. Dynamic SQL

SET SERVEROUTPUT ON
EXEC rename_demo_p_dynamic;
DYNAMIC COUNT = 3

PL/SQL procedure successfully completed.

7. 스키마 단위 재컴파일 실험

스키마 전체 재컴파일

BEGIN
    DBMS_UTILITY.compile_schema(schema => USER);
END;
/

다시 확인

SELECT object_name, object_type, status
FROM   user_objects
WHERE  object_name LIKE 'RENAME_DEMO_%'
ORDER BY object_type, object_name;

OBJECT_NAME                                        OBJECT_TYPE                    STATUS
-------------------------------------------------- ------------------------------ -------
RENAME_DEMO_F_GET_NAME                             FUNCTION                       VALID
RENAME_DEMO_MV_EMP                                 MATERIALIZED VIEW              VALID
RENAME_DEMO_PKG                                    PACKAGE                        VALID
RENAME_DEMO_PKG                                    PACKAGE BODY                   VALID
RENAME_DEMO_P_DYNAMIC                              PROCEDURE                      VALID
RENAME_DEMO_P_EMP_CNT                              PROCEDURE                      VALID
RENAME_DEMO_EMP_SYN                                SYNONYM                        VALID
RENAME_DEMO_EMP                                    TABLE                          VALID
RENAME_DEMO_EMP_REN                                TABLE                          VALID
RENAME_DEMO_MV_EMP                                 TABLE                          VALID
RENAME_DEMO_TRG_BI_EMP                             TRIGGER                        VALID
RENAME_DEMO_V_EMP                                  VIEW                           VALID

12 rows selected.

참조가 모두 정상인 오브젝트는 VALID
- 스키마 단위 재컴파일 실행 시 SYNONYM 역시 VALID가 되는 모습
만약 의도적으로 문법 오류를 섞어놓았다면 그건 다시 INVALID로 남아 있음

8. 결론

테이블명 변경 시 의존성 있는 오브젝트 영향도
- 정적 SQL 기반 오브젝트들은 대부분 INVALID로 떨어짐(테이블명이 스크립트에 포함되지 않은 Package는 제외됨)
- 동적 SQL 기반 오브젝트는 VALID 유지
테이블 대체 시
- INVALID였던 오브젝트들이 자동으로 VALID로 복귀하지 않음
- 오브젝트 실행 or 수동 재컴파일이 있어야 VALID로 변경
  → 스키마 단위 재컴파일 시 SYNONYM도 VALID로 변경됨
동적 SQL의 위험성 확인
- 테이블명 변경 시 Dynamic SQL의 STATUS는 VALID이지만, 실제로는 죽은 코드가 됨(오브젝트 실행 시 ORA-00942 에러가 발생)
  → dependency 뷰에 안 잡히기 때문에 영향도 분석 시 놓치기 쉬움

-- 동적 SQL 프로시저 실행: ORA-00942
SQL> EXEC rename_demo_p_dynamic;

ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at "TEST.RENAME_DEMO_P_DYNAMIC", line 4
ORA-06512: at line 1

9. 참고: 정리용 DROP 스크립트

DROP MATERIALIZED VIEW rename_demo_mv_emp;
DROP SYNONYM rename_demo_emp_syn;

DROP TRIGGER rename_demo_trg_bi_emp;
DROP PACKAGE rename_demo_pkg;
DROP PROCEDURE rename_demo_p_emp_cnt;
DROP PROCEDURE rename_demo_p_dynamic;
DROP FUNCTION rename_demo_f_get_name;
DROP VIEW rename_demo_v_emp;
DROP TABLE rename_demo_emp PURGE;
DROP TABLE rename_demo_emp_ren PURGE;

728x90

'📁 Database > Oracle' 카테고리의 다른 글

[Oracle] DBA_HIST_ACTIVE_SESS_HISTORY (ASH의 과거 데이터) (0)	2025.11.28
[Oracle] ORA-08177: can't serialize access for this transaction (0)	2025.11.17
[Oracle] Consistent Read(CR) / Current Read(CU) (0)	2025.11.13
[Oracle] 쿼리 수행 후 실행 통계 확인 (SET AUTOTRACE ON STATISTICS) (0)	2025.11.13
[Oracle] Lock과 Latch (0)	2025.11.12

0. 전제

1. 기본 테이블 + 샘플 데이터 생성

2. 의존 오브젝트들 생성

3. 현재 오브젝트 상태 확인

4. 테이블명 변경 (A → C)

5. 테이블명 변경 (B → A)

6. 객체 단위 재컴파일 실험

6-1. procedure 재컴파일

6-2. function 재컴파일

6-3. PACKAGE 재컴파일

6-4. TRIGGER 재컴파일

6-5. MATERIALIZED VIEW LOG 재컴파일

6-6. View 재컴파일

6-7. SYNONYM

6-8. Dynamic SQL

7. 스키마 단위 재컴파일 실험

8. 결론

9. 참고: 정리용 DROP 스크립트

'📁 Database > Oracle' 카테고리의 다른 글

티스토리툴바