[OracleDB] 공통 Invalid Object Compile 방법

// Subject : [OracleDB] 공통 Invalid Object Compile 방법

// Tag : Oracle DB Invalid Object Compile Manually Recompile script

 

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

 

End of document.