ORA-4068 or ORA-04068 "existing state of packages%s%s%s has been discarded" ORA-4061 or ORA-04061 "existing state of %s has been invalidated" ---------------------------------------------------------------------------------------------------- [Update for 11gR2] Oracle 11.2.0.2 enhancement: http://download.oracle.com/docs/cd/E11882_01/server.112/e17128/chapter1_2.htm#FEATURENO09126 "A package, whose global variables is marked constant; and where the initial value can be computed at compile time, is now considered to be stateless. "The new procedure DBMS_SESSION.RESET_SELF(), whose invocation must be in the package of interest, allows you to preempt the problem when it is safe to reinitialize the package state." ---------------------------------------------------------------------------------------------------- [Main article] The minimalist test case: (from http://www.freelists.org/post/oracle-l/Which-sessions-hold-state-on-which-packages,10) In session 1: create or replace package pkg as var number; end; / exec pkg.var := 1 In session 2: create or replace package pkg /* xxx */ as var number; end; / In session 1: SQL> exec pkg.var := 1 BEGIN pkg.var := 1; END; * ERROR at line 1: ORA-04068: existing state of packages has been discarded ORA-4068 error means that if a PL/SQL package uses a global variable, and a "foreign" session compiles or re-creates the package between two executions of the package in your session, your second execution will find that the package state is no longer valid. Some applications are sensitive to this error, so prediction is desired, and total prevention would be far better. Based on SR 2-5770808 (legacy 7658001.994), enhancement "Bug 8853896 (SQL SCRIPT TO PREDICT POTENTIAL OBJECTS WHICH WILL THROW ORA-4068)" was filed. For 10g, the SQL below is modified from catblock.sql incorporating Tanel Poder's finding (the last line) that the 8th bit of kgllkflg signifies broken kgl lock: col type for a25 col owner for a20 col name for a30 select s.sid session_id, substr(ob.kglnaown,1,30) owner, substr(ob.kglnaobj,1,30) name, decode(ob.kglhdnsp, 0, 'Cursor', 1, 'Table/Procedure/Type', 2, 'Body', 3, 'Trigger', 4, 'Index', 5, 'Cluster', 13, 'Java Source', 14, 'Java Resource', 32, 'Java Data', to_char(ob.kglhdnsp)) type, decode(lk.kgllkmod, 0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive', 'Unknown') mode_held, decode(lk.kgllkreq, 0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive', 'Unknown') mode_requested from v$session s, x$kglob ob, x$kgllk lk where lk.kgllkhdl = ob.kglhdadr and lk.kgllkuse = s.saddr and ob.kglhdnsp != 0 and bitand(kgllkflg,256)=256; Sample output: SESSION_ID OWNER NAME TYPE MODE_HELD MODE_REQU ---------- ----------- -------------- -------------------- --------- --------- 1070 SYS DATABASE 18 Null None 1064 SYS DATABASE 18 Null None 1072 SYS DATABASE 18 Null None 991 APP_ACCOUNT PKGAPPMESSAGES Body Null None 991 APP_ACCOUNT PKGAPPMESSAGES Table/Procedure/Type Null None 945 APP_ACCOUNT PKGAPPMESSAGES Body Null None 945 APP_ACCOUNT PKGAPPMESSAGES Table/Procedure/Type Null None 1041 APP_ACCOUNT PKGAPPMESSAGES Body Null None 1041 APP_ACCOUNT PKGAPPMESSAGES Table/Procedure/Type Null None 1001 APP_ACCOUNT PKGAPPMESSAGES Body Null None 1001 APP_ACCOUNT PKGAPPMESSAGES Table/Procedure/Type Null None (I think we can restrict type to Body.) Once the prediction is made, as shown in the above SQL output, you may notify the users of those sessions so they can be prepared to handle the error, by retrying to execute, for instance. The reason that ORA-4068 does not occur on the next execution (after the one that throws the error) is that the session global variable value is re-initialized. But this is a problem for a scheduled job, which, as a non-human, does nott know it needs a retry, unless it's programmed to do so. There's no ideal solution. The application or user can precede the execution with dbms_session.modify_package_state or (a little outdated) dbms_session.reset_package, or as Eddie Awad noted (http://awads.net/wp/2007/04/04/here-is-how-to-unpersist-your-persistent-plsql-package-data/), use serially_reusable pragma. But always calling either of the two procedures in dbms_session or always using serially_reusable adds overhead, apart from undesired side effect. So a wise solution may be adding a little logic to catch the error and retry once when an error occurs. Or consider not using any global variable at all. ---------------------------------------------------------------------------------------------------- Catbundle causes sessions using text index to throw ORA-4068 on RAC http://www.freelists.org/post/oracle-l/Catbundle-causes-sessions-using-text-index-to-throw-ORA4068-on-RAC On two Oracle 10.2.0.4.0 RAC databases, after we applied Oct-2009 CPU patch, both times some users got errors similar to the following: ORA-29877: failed in the execution of the ODCIINDEXUPDATE routine ORA-20000: Oracle Text error: DRG-50857: oracle error in textindexmethods.ODCIIndexUpdate ORA-06502: PL/SQL: numeric or value error: character to number conversion error ORA-06512: at "CTXSYS.DRIXMD", line 438 ORA-04061: existing state of package body "CTXSYS.DRIUTL" has been invalidated ORA-04065: not executed, altered or dropped package body "CTXSYS.DRIUTL" ORA-06508: PL/SQL: could not find program unit being called: "CTXSYS.DRIUTL" ORA-06 SQL Statement: ... This is because at the end of the patch, $ORACLE_HOME/rdbms/admin/catbundle.sql is run. On RAC, we apply the patch on local node, one node at a time. Before the patch, VIP is relocated to another node and the services on this node are stopped. So apps are running on other nodes. On the last node only, we run catbundle.sql, which includes @?/ctx/admin/driutl.plb which replaces ctxsys.driutl package body. If a session on *any* node of the RAC updates a table using a text index after driutl package body is replaced with new code, the session will get the above error. (If it's a delete, the routine on the first line will be ODCIINDEXDELETE). This means that the rolling nature of the patch has a risk on production. All solutions need application code change. Since CPU patch is done only once per three months, for us, it's not worth it. The package driutl is not changed much. I'm considering commenting out the line in catbundle_CPU__APPLY.sql and manually run it. (Also need to comment out the last line "@&sf" in catbundle.sql.) If you need to reproduce the error: create table testctx as select rownum id, object_name name from user_objects; create index indctx_testctx on testctx (name) indextype is ctxsys.context; In another session (invalidate instead of replacing the package, because we don't have slightly modified code which is needed in 10g and up): exec dbms_utility.invalidate() Back to the first session, run any of the following: select * from testctx where contains(name, '') > 0; delete from testctx where id = 1; update testctx set name = 'xx' where id = 1;