공통 Invalid Object Compile 방법

[ 출처 : http://datacloud.tistory.com/ ]

1. Oracle DB 에서 왜 주기적인 Invalid Object ReCompile 이 필요한가 ?
  아래의 여러 가지 사유들로, Object 들이 Invalid 상태로 빠지게 됩니다.
  - Oracle DB 에 대한 Major, Minor Upgrade 수행 후
  - Function 이 사용하는 Table 의 Column 추가/삭제/변경이 발생되는 경우

  보통은 Object 들을 CALL/USE 하게 되면, 다시 Valid 상태로 자동 Compile 이 되나,
  때로는 아래의 문제들을 발생시키기도 합니다. DB 장애의 원인이 되기도 합니다.
  - ORA-4020 : Deadlock 이 감지된 상태에서의 library object 에 대한 lock을 획득 실패, <Note:130409.1> 참조
  - ORA-00911 : User miss 로 (Descriptive Flexfield 등을 신규생성/수정 시, Segment name 의 첫 CHARACTER 로 숫자나 알파벳 혹은 한글이 아닌 특수문자(ex> "[]")를 사용) Object 가 Compile 불가 상태로 변경된 경우

  따라서, DBA 들은, 주기적으로 Invalid Object 들을 조사해서, 사전에 Recompile 을 통해, 관련 Object 들의 문제를 조치하고, 예기치 못한 Issue 발생에 대비하고 있습니다.


2. Invalid Object 를 Re compile 하는 방법
  기본적으로 Oracle DB 에는 Invalid Object 들을 Compile 하는 5가지 방법이 존재합니다.

  - DBMS_DDL
  - DBMS_UTILITY
  - UTL_RECOMP
  - UTLRP.SQL
  - Manually Recompile

 저는 간단한 Script 를 이용해서, Manual recompile 하기를 좋아 하는데, text mode 의 telnet 창에 접속하여, 다수의 서버들에서 logging 하면서 동시 작업하기가 편하기 때문입니다. 각각의 방법들은 장단점들이 있지만, 여기서는 언급하지 않고, 수행 방법들만 기술하도록 하겠습니다.


2-A. DBMS_DDL.ALTER_COMPILE
  Definition : ALTER PROCEDUREFUNCTIONPACKAGE [.] COMPILE [BODY]
  Syntax : Exec dbms_ddl.alter_compile ( type , schema, name);
  - Type : Must be either PROCEDURE, FUNCTION, PACKAGE, PACKAGE BODY or TRIGGER.

  Example

  SQL> exec dbms_ddl.alter_compile ('PROCEDURE','SCOTT','TEST');
      PL/SQL procedure successfully completed.
  SQL>

2-B. DBMS_UTILITY.COMPILE_SCHEMA
  Definition : This procedure compiles all procedures, functions, packages, and triggers in the specified schema.
  Syntax : Exec dbms_utility.compile_schema ( schema,compile all)
  - Compile All : Object type ( procedure, function, packages,trigger)

  Example

  SQL> exec dbms_utility.compile_schema('SCOTT');
       PL/SQL procedure successfully completed.
  SQL>

2-C. UTL_RECOMP
  Definition : This script is particularly useful after a major-version upgrade that typically invalidates all PL/SQL and Java objects.
  Syntax : Exec UTL_RECOMP.RECOMP_SERIAL ();
  - Note: Required SYS user to run this package.

  Example

  SQL> Exec UTL_RECOMP.RECOMP_SERIAL ();
       PL/SQL procedure successfully completed.
  SQL>

2-D. UTLRP.SQL scripts

  Definition : Recompiles all existing PL/SQL modules that were previously in an INVALID state, such as packages, procedures, and types.
  Syntax : Located: $ORACLE_HOME/rdbms/admin
  - Note: Required SYS user to run this script.
  - Recommended: After upgrade or migrate database.

  Example

  SQL> @ c:\oracle\product\10.2.0\db_1\rdbms\admin\UTLRP.SQL
    TIMESTAMP
    -----------------------------------------------------------------------
    COMP_TIMESTAMP UTLRP_BGN 2012-02-03 10:40:22
    PL/SQL procedure successfully completed.
    COMP_TIMESTAMP UTLRP_END 2012-02-03 10:40:29
    PL/SQL procedure successfully completed.
  SQL>

2-E. Manually recompiling : Best Approach to Recompile all Invalid Objects
  Syntax : ALTER OBJECT_TYPE.OBJECT_NAME COMPILE ;
  - Note: VIEW, SYNONYM, PUBLIC SYNONYM, PROCEDURE, FUNCTION, PACKAGE, PACKAGE BODY, TRIGGER, UNDEFINED (MATERIALIZED VIEW), JAVA CLASS, TYPE, TYPE BODY

  Example : Object Type 에 따라, 몇 개의 Script 로 나눌 수 있습니다.

  SQL> Spool recompile.sql
  SQL> Select ‘alter ‘object_type’ ’object_name’ compile ; ’ From user_objects where status <> ‘VALID’ and object_type IN (‘VIEW’, ’SYNONYM’, ‘PROCEDURE’, ’FUNCTION’,     ‘PACKAGE’, ’TRIGGER’) ;
  SQL> spool off
  SQL> @ recompile.sql

  - Note : VIEW, SYNONYM, PROCEDURE, PACKAGE, FUNCTION, TRIGGER

  SQL> Spool pkg_body.sql
  SQL> Select ‘alter package ’object_name’ compile body ; ’ from user_objects where status <> ‘VALID’ and object_type = ‘PACKAGE BODY’ ;
  SQL> spool off
  SQL> @ pkg_body.sql

  - Note : PACKAGE BODY

  SQL> Spool undefined.sql
  SQL> select ‘alter materizlized view ’object_name’ compile ; ’ from user_objects where status <> ‘VALID’ and object_type =‘UNDEFINED’ ;
  SQL> spool off
  SQL> @ undefined.sql

  - Note : UNDEFINED

  SQL> Spool javaclass.sql
  SQL> Select ‘alter java class ’object_name’ resolve ; ’ from user_objects where status <> ‘VALID’ and object_type =‘JAVA CLASS’ ;
  SQL> spool off
  SQL> @ javaclass.sql

  - Note : JAVA CLASS’

  SQL> Spool typebody.sql
  SQL> Select ‘alter type ‘object_name’ compile body ; ’ from user_objects where status <> ‘VALID’ and object_type =‘TYPE BODY’ ;
  SQL> spool off
  SQL> @ typebody.sql

  - Note : TYPE BODY

  SQL> Spool public_synonym.sql
  SQL> Select ‘alter public synonym ‘object_name’ compile ; ’ from user_objects where status <> ‘VALID’ and owner = ‘PUBLIC’ and object_type = ‘SYNONYM’ ;
  SQL> spool off
  SQL> @ public_synonym.sql

  - Note : PUBLIC SYNONYM