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
- 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>
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>
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>
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>
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, TRIGGERSQL> 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
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 BODYSQL> 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
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 : UNDEFINEDSQL> select ‘alter materizlized view ’object_name’ compile ; ’ from user_objects where status <> ‘VALID’ and object_type =‘UNDEFINED’ ;
SQL> spool off
SQL> @ undefined.sql
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> Select ‘alter java class ’object_name’ resolve ; ’ from user_objects where status <> ‘VALID’ and object_type =‘JAVA CLASS’ ;
SQL> spool off
SQL> @ javaclass.sql
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 BODYSQL> Select ‘alter type ‘object_name’ compile body ; ’ from user_objects where status <> ‘VALID’ and object_type =‘TYPE BODY’ ;
SQL> spool off
SQL> @ typebody.sql
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
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
