출처 : http://tip.daum.net/question/62996518
ORA-06508 PL/SQL: could not find program unit being called ~
<< 환경 >>
인터페이스 모듈을 프로시져로 작성하고 있습니다.
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
인스턴스 instance-1 이 있구요.
인스턴스 안에 스키마 user-1 , user-2가 있습니다.
user-2에서 프로시져를 작성했구요 프로시져 안에서 user-1 에서 만든 패키지 pkg_1를 콜합니다.
pkg_1은 user-2의 다른 pkg_2를 콜합니다.
( user_2.프로시져 -> user_1.pkg_1 -> user_2.pkg_2 ) 이런 식입니다.
<< 에러현상 재현>>
여러분들이 많이 쓰시는 Toad나 Orange, Develper 등에서 user_2로 login해서 작업합니다.
작업1] session-1st를 생성 -> 1st session에서 프로시져를 실행했을때 문제없이 돌아갑니다.
작업2] session-2nd를 생성 -> 2nd session에서 프로시져를 실행했을때 마찬가지로 문제없이 돌아갑니다.
작업3] session-1st에서 프로지져안에서 콜하는 pkg_2를 컴파일 합니다. (user_2는 권한가지고있음)
(이때 pkg_1은 invalid 상태로 변합니다.)
작업4] session-2nd에서 해당 프로시져를 콜하면 위와 같은 에러메시지가 나옵니다.
작업5] session-1st에서 invalid인 pkg_1을 재컴파일하지 않은 상태에서 프로지져실행 -> 잘돌아갑니다. (이때 invalid한 pkg_1은 valid한 상태로 바뀝니다.)
작업6] session-2nd에서 프로시져를 실행 -> 같은 에러가 나옵니다.
작업7] session-3rd를 생성 -> 3rd session에서 프로시져를 실행했을때 문제없이 돌아갑니다.
작업8] session-2nd에서 프로시져를 실행 -> 같은 에러가 나옵니다
==> 정리하면 pkg_2를 컴파일하는 session은 전혀 문제가 없습니다.
==> session이 유지되고 있는 상태에서 다른 session이 자신이 access하는 pkg_2를 컴파일 하면 해당 session은 에러를 발생시키지만...새로 맺는 session은 에러를 발생시키지 않습니다.
이 현상을 파악하는데....1개월이 걸렸습니다.
그런데..해결 방안이 떠오르질 않네요..
좀 더 구체적으로 말씀드리자면...session-2nd는 EAI SERVER에서 Connection Pool이라고 생각하시면 됩니다. 그러니...새로운 .session을 맺으라는 얘기는 db를 restart하라는 얘기와 같습니다.
[[제 생각]]
제가 알기론 db가 구동될때...ORACLE SUB_PROGRAM(fn,sp,pkg등)정보를 Dictionary cache에 올리는 걸로 알고 있는데...이쪽에 원인이 있다고 생각하는데..(동기화등등 ) 암튼 지금..넘 막연하네요.
조치는 해야 하는데..하루하루가...죽을맛입니다...
그럼 부탁드리구요..좋은 하루 되세요...
-----------------------------
pl/sql의 hot deploy가 안되는 경우입니다....
1. dependency가 아주 깊이 있는 경우 invalid된 package가 자동 compile이 안되는 경우가 있읍니다.
2. 수정에 의해 invalid된 package안에서 package level의 변수(전역변수)를 사용한 경우
발생할수 있읍니다.
아래의 오라클 문서를 참고하세요.
ORA-04068 Errors from User-Written and Oracle Packages | ||||
Doc ID: | Note:106206.1 | Type: | BULLETIN | |
Last Revision Date: | 14-NOV-2006 | Status: | PUBLISHED |
Problem Description ------------------- You receive the following error from user-written and Oracle packages: ORA-04068 existing state of packages%s%s%s has been discarded Causes of ORA-4068 Errors ------------------------- 1) A dependent object was altered through a DDL statement. When a package is compiled, all copies in the shared pool are flagged as invalid. The next invocation of the package sees this flag set, and goes to get a new copy. If the package is now invalid, cannot compile, or relied on a package state (i.e., package level variables), then this error occurs because the current copy of the package is no longer valid and must be thrown out. 2) The package was changed or recompiled (both DDL) and the package being used contains package level variables. Same as above. When a package dependency is altered through DDL statements (DROP, CREATE, ALTER, ...), this package is flagged as invalid through cascade invalidation. 3) A package relied on another package that had no body, and during execution, the call failed. When a package is compiled, it only looks for the specification. During execution, it calls a non-existent routine and throws an error. This error then invalidates the package. Another variation is if the procedure being called is not defined in the package body and possibly as a standalone routine. 4) A remote dependent object has been altered through a DDL statement. This can occur between database instances or from Forms or Reports to a database instance. The default remote dependency model uses the Timestamp model, and when an execution of a procedure takes place, the remote object's timestamp is validated, thus forcing invalidation on the local package. To check for these situations, several SQL statements can be run: a. To check the package's last compile: SELECT object_name, object_type, owner, status, last_ddl_time FROM dba_objects WHERE object_name = '<PACKAGE NAME>'; For example: SQL> SELECT object_name, object_type, owner, status, last_ddl_time FROM 2 dba_objects WHERE object_name = 'DBMS_SQL'; OBJECT_NAME ------------------------------------------------------------------------ OBJECT_TYPE OWNER STATUS LAST_DDL_ ------------- ------------------------------ ------- --------- DBMS_SQL PACKAGE SYS VALID 13-JUL-99 DBMS_SQL PACKAGE BODY SYS VALID 13-JUL-99 DBMS_SQL SYNONYM PUBLIC VALID 13-JUL-99 SQL> b. To check the dependent objects last alteration: SELECT object_name, object_type, owner, status, last_ddl_time FROM dba_objects WHERE ( object_name, object_type ) IN ( SELECT referenced_name, referenced_type FROM dba_dependencies WHERE name = '<PACKAGE NAME>' ); For example: SQL> SELECT object_name, object_type, owner, status, last_ddl_time FROM 2 dba_objects WHERE ( object_name, object_type ) IN ( SELECT 3 referenced_name, referenced_type FROM dba_dependencies WHERE name = 4 'DBMS_SQL' ); OBJECT_NAME ----------------------------------------------------------------------------- OBJECT_TYPE OWNER STATUS LAST_DDL_ ------------- ------------------------------ ------- --------- DBMS_SQL PACKAGE SYS VALID 13-JUL-99 DBMS_SYS_SQL PACKAGE SYS VALID 13-JUL-99 STANDARD PACKAGE SYS VALID 13-JUL-99 SQL> c. To check for existing errors on package: SELECT name, type, text FROM dba_errors WHERE name = '<PACKAGE NAME>'; For example: SQL> SELECT name, type, text FROM dba_errors WHERE name = 'DBMS_SQL'; no rows selected SQL> Solutions for ORA-4068 Errors ----------------------------- 1) Simply re-execute the package. For example: Session 1: Create the package and body for package p_pack: SQL> create or replace package p_pack as 2 p_var varchar2(1); 3 procedure p; 4 end p_pack; 5 / Package created. SQL> create or replace package body p_pack as 2 procedure p is 3 begin 4 p_var := 1; 5 end; 6 end p_pack; 7 / Package body created. SQL> Session 2: Execute the package: SQL> exec p_pack.p PL/SQL procedure successfully completed. SQL> Session 1: Recreate the package and body: SQL> create or replace package p_pack as 2 p_var varchar2(1); 3 procedure p; 4 end p_pack; 5 / Package created. SQL> create or replace package body p_pack as 2 procedure p is 3 begin 4 p_var := 1; 5 end; 6 end p_pack; 7 / Package body created. SQL> Session 2: Re-execute the package: SQL> exec p_pack.p begin p_pack.p; end; * ERROR at line 1: ORA-04068: existing state of packages has been discarded ORA-04061: existing state of package "SCOTT.P_PACK" has been invalidated ORA-04065: not executed, altered or dropped package "SCOTT.P_PACK" ORA-06508: PL/SQL: could not find program unit being called ORA-06512: at line 1 SQL> exec p_pack.p PL/SQL procedure successfully completed. SQL> As background, when the ORA-4068 is raised, ORACLE will throw away all existing instantiations of the package. When the package (more properly, the subprogram referring to the package) is re-executed, ORACLE will re-instantiate the package automatically (if possible), which typically will succeed, and re-execution of the subprogram will succeed. An important proviso is that the ORA-4068 error must be unhandled on exit from the subprogram in order for this solution to work. It's only when an _unhandled_ ORA-4068 is returned by PL/SQL to ORACLE that the needed deinstantiations take place. (The ORA-4068 may be handled in the subprogram and various actions taken in the handler, but the error must be reraised in order for it to be unhandled on exit from PL/SQL to get the desired deinstantiations.) 2) Attempt to recompile the package by using the ALTER PACKAGE command. For example: SQL> ALTER PACKAGE DBMS_SQL COMPILE; Package altered. SQL> 3) Verify that proper execute permissions have been provided. In PL/SQL stored program units, roles are disabled prior to the release of Oracle 8i. Oracle 8i definers rights follow the previous release model of requiring explicit permission to the object. In Oracle 8i, if invoker's rights are set on the routine, then execution is done with roles enabled, so permission could be granted explicitly to the schema executing or through a role. For additional information, refer to Note 162489.1 entitled "Invokers Rights Procedure Executed by Definers Rights Procedures". For example: SQL> SELECT owner, table_name, privilege FROM dba_tab_privs WHERE 2 table_name = 'DBMS_SQL' AND ( grantee = 'SCOTT' OR grantee = 'PUBLIC' ); OWNER TABLE_NAME ------------------------------ ------------------------------ PRIVILEGE ---------------------------------------- SYS DBMS_SQL EXECUTE SQL> 4) If errors exist (check DBA_ERRORS or USER_ERRORS views with above query), then take the appropriate action to correct the errors. If the package is a system package that comes with the Oracle server, the scripts are located in $ORACLE_HOME/rdbms/admin. Most packages have their own .sql and .plb script to build the specification and body (see below for names). System packages that come with the Oracle server, as well as other Oracle products, typically need to be owned by a particular schema. In the case of the Oracle server DBMS packages, these need to be owned by SYS. If these packages are not owned by SYS, some packages start getting 'ORA-6509 PL/SQL ICD vector missing for this package' errors. 5) If duplicate SYS owned objects exist, clean them up. Refer to Note 1030426.6, entitled "HOW TO CLEAN UP DUPLICATE OBJECTS OWNED BY SYS AND SYSTEM SCHEMA", for information on this. $ORACLE_HOME/rdbms/admin Packages --------------------------------- System Package Name Package Spc Package Bdy Owner ------------------- ----------- ----------- ----- DBMS_ALERT dbmsalrt.sql prvtalrt.plb SYS DBMS_APPLICATION_INFO dbmsapin.sql prvtapin.plb SYS DBMS_AQ dbmsaq.plb prvtaq.plb SYS DBMS_AQADM dbmsaqad.sql prvtaqad.plb SYS DBMS_CRYPTO_TOOLKIT dbmsoctk.sql prvtoctk.plb SYS DBMS_DDL dbmsutil.sql prvtutil.plb SYS DBMS_DEBUG dbmspb.sql prvtpb.plb SYS DBMS_DEFER dbmsdefr.sql prvtdefr.plb SYS DBMS_DEFER_QUERY dbmsdefr.sql prvtdefr.plb SYS DBMS_DEFER_SYS dbmsdefr.sql prvtdefr.plb SYS DBMS_DESCRIBE dbmsdesc.sql prvtdesc.plb SYS DBMS_DISTRIBUTED_TRUST_ADMIN dbmstrst.sql prvttrst.plb SYS DBMS_HS dbmshs.sql prvths.plb SYS DBMS_IOT dbmsiotc.sql prvtiotc.plb SYS DBMS_JOB dbmsjob.sql prvtjob.plb SYS DBMS_LOB dbmslob.sql prvtlob.plb SYS DBMS_LOCK dbmslock.sql prvtlock.plb SYS DBMS_LOGMNR dbmslm.sql prvtlm.plb SYS DBMS_LOGMNR_D dbmslmd.sql dbmslmd.sql SYS DBMS_OFFLINE_OG dbmsofln.sql prvtofln.plb SYS DBMS_OFFLINE_SNAPSHOT dbmsofsn.sql prvtofsn.plb SYS DBMS_ORACLE_TRACE_AGENT dbmsotrc.sql prvtotrc.plb SYS DBMS_ORACLE_TRACE_USER dbmsotrc.sql prvtotrc.plb SYS DBMS_OUTPUT dbmsotpt.sql prvtotpt.plb SYS DBMS_PCLXUTIL dbmsutil.sql prvtutil.plb SYS DBMS_PIPE dbmspipe.sql prvtpipe.sql SYS DBMS_RANDOM dbmsrand.sql dbmsrand.sql SYS DBMS_RECTIFIER_DIFF dbmsrctf.sql prvtrctf.plb SYS DBMS_REFRESH dbmssnap.sql prvtsnap.plb SYS DBMS_REPAIR dbmsrpr.sql prvtrpr.plb SYS DBMS_REPCAT dbmshrep.sql prvtbrep.plb SYS DBMS_REPCAT_ADMIN prvthdmn.plb prvtbdmn.plb SYS DBMS_REPCAT_INSTANTIATE dbmsrint.sql prvtbrnt.plb SYS DBMS_REPCAT_RGT dbmsrgt.sql prvtbrgt.plb SYS DBMS_REPUTIL dbms_gen.sql prvtgen.plb SYS DBMS_RESOURCE_MANAGER dbmsrmad.sql prvtrmad.plb SYS DBMS_RESOURCE_MANAGER_PRIVS dbmsrmpr.sql prvtrmpr.plb SYS DBMS_RLS dbmsrlsa.sql prvtrlsa.plb SYS DBMS_ROWID dbmsutil.sql prvtutil.plb SYS DBMS_SESSION dbmsutil.sql prvtutil.plb SYS DBMS_SHARED_POOL dbmspool.sql prvtpool.plb SYS DBMS_SNAPSHOT dbmssnap.sql prvtsnap.plb SYS DBMS_SPACE dbmsutil.sql prvtutil.plb SYS DBMS_SPACE_ADMIN dbmsspc.sql prvtspad.plb SYS DBMS_SQL dbmssql.sql prvtsql.plb SYS DBMS_STATS dbmsstat.sql prvtstat.plb SYS DBMS_TRACE dbmspbt.sql prvtpbt.plb SYS DBMS_TRANSACTION dbmsutil.sql prvtutil.plb SYS DBMS_UTILITY dbmsutil.sql prvtutil.plb SYS OUTLN_PKG dbmsol.sql prvtol.plb SYS UTL_COLL utlcoll.sql prvtcoll.plb SYS UTL_FILE utlfile.sql prvtfile.plb SYS UTL_HTTP utlhttp.sql prvthttp.plb SYS UTL_RAW utlraw.sql prvtrawb.plb SYS UTL_REF utlref.sql prvtref.plb SYS References ---------- "Oracle7 Server Application Developer's Guide", (A32536-1) "Oracle8 Server Application Developer's Guide", (A54642-01) "Oracle8i Application Developer's Guide - Fundamentals Release 8.1.5", (A68003-01) Related Articles ---------------- Note 19857.1 OERR: ORA 4068 "existing state of packages%s%s%s has been discarded" Note 19854.1 OERR: ORA 4065 "not executed, altered or dropped %s" Note 19850.1 OERR: ORA 4061 "existing state of %s has been invalidated" Note 20065.1 OERR: ORA 6508 "PL/SQL: could not find program unit being called" Note 1012129.102 ORA-4068,4067,6508 When Executing a Procedure Using PL/SQL Note 117118.1 Errors Running or Compiling DBMS_RANDOM or DBMS_CRYPTO_TOOLKIT Note 1030426.6 HOW TO CLEAN UP DUPLICATE OBJECTS OWNED BY SYS AND SYSTEM SCHEMA Note 1012129.102 ORA-4068,4067,6508 WHEN EXECUTING A PROCEDURE USING PLSQL Related Errors -------------- ORA-4061 ORA-4063 ORA-4065 ORA-4067 ORA-4068 ORA-4088 ORA-6508 ORA-6509 ORA-6568 ORA-6512