11 Replies Latest reply on Mar 20, 2013 8:32 AM by 989129

    ORA-04068: existing state of packages has been discarded

    989129
      How to resolve ORA-04068: existing state of packages has been discarded
      We are facing issue in production and its happening intermittently.

      But without compiling the package the application is working fine next time onwards.
        • 1. Re: ORA-04068: existing state of packages has been discarded
          LaserSoft
          986126 wrote:
          How to resolve ORA-04068: existing state of packages has been discarded
          We are facing issue in production and its happening intermittently.

          But without compiling the package the application is working fine next time onwards.
          As per the error code it looks more issue with initilization of applications, found any more information and log from application end?
          oerr ora 4068
          04068, 00000, "existing state of packages%s%s%s has been discarded"
          // *Cause:  One of errors 4060 - 4067 when attempt to execute a stored
          //          procedure.
          // *Action: Try again after proper re-initialization of any application's
          //          state.
          • 2. Re: ORA-04068: existing state of packages has been discarded
            Purvesh K
            986126 wrote:
            How to resolve ORA-04068: existing state of packages has been discarded
            We are facing issue in production and its happening intermittently.

            But without compiling the package the application is working fine next time onwards.
            There are many reasons due to which this may happen:
            1. Is there any manual/automatic compiling of Object/schema, via a Job etc.?
            2. Is any dependent Object modified, manually or via Dynamic SQL?
            3. Is there any pattern in which the packages are in un-compiled state?

            It is difficult to ascertain the reason without having proper details at hand. And it will only be a Guessing game, unless you eliminate some of the reasons.

            IMO, you should first pick out the Jobs/PLSQL code to verify if it does not dynamically alter the objects, which package is referring to.
            • 3. Re: ORA-04068: existing state of packages has been discarded
              989129
              There is no automatic compiling of schema or object in place.
              No depenedant object are getting modified via any packages/ dynamic SQL in procedure/trigger
              • 4. Re: ORA-04068: existing state of packages has been discarded
                _
                Although it doesn't solve the issue of packages being recompiled, you could modify the package body or spec so that it is stateless, or in other words, remove any global variables from the package (either from the body into the spec, or into a separate package which is solely used for global variables). If you do this, the packages will automatically recompile without raising ORA-04068. There is an overhead to the recompilation so would be good to resolve this also. Check whether any dependent objects (in user_dependencies view) have any DDL operations carried out on them as a starting point. You can also check the last_ddl_time column in user_objects to determine recent DDL.
                1 person found this helpful
                • 5. Re: ORA-04068: existing state of packages has been discarded
                  Purvesh K
                  986126 wrote:
                  There is no automatic compiling of schema or object in place.
                  No depenedant object are getting modified via any packages/ dynamic SQL in procedure/trigger
                  You can check the DDL's against the objects
                  select object_type, object_name, last_modified_time
                    from dba_objects
                  order by last_modified_time desc;
                  This will give you the Objects that have been most recently modified.
                  • 6. Re: ORA-04068: existing state of packages has been discarded
                    Osama_Mustafa
                    You didn't post your DB version , OS version What is your setup RAC or single node ?
                    • 7. Re: ORA-04068: existing state of packages has been discarded
                      989129
                      Thanks Mike.
                      i think this would be what i expected.
                      Can you please eloborate
                      either from the body into the spec, or into a separate package which is solely used for global variables.

                      Also please let me know
                      Whether usage of when others than exception will cause the package discarded issue?
                      • 8. Re: ORA-04068: existing state of packages has been discarded
                        _
                        Hi, if your package has global variables, it can have a state. In any particular session, the values of any global variables will persist. As the error message describes, the state has been discarded. So if another user has recompiled the package, or the package has been recompiled automatically, this error will result in other sessions that have accessed the package. This error will only occur once, and on the second attempt the package will be recompiled without error. The reasons for the package being recompiled could be that it has been compiled manually, or that other code on which it depends has been compiled, or that any object (eg. table) on which the package depends has been modified. A when other exception in itself won't cause this problem. You can avoid the error message by removing global variables. If the global variables are required, they could be moved to another package, or to the package spec.
                        1 person found this helpful
                        • 9. Re: ORA-04068: existing state of packages has been discarded
                          989129
                          We are using RAC DAtabase
                          DB Version is 11.2.0.2
                          OS Version is RHEL 5
                          • 10. Re: ORA-04068: existing state of packages has been discarded
                            Osama_Mustafa
                            Check
                            ORA-04068 error on RAC environment [ID 1470523.1]
                            • 11. Re: ORA-04068: existing state of packages has been discarded
                              989129
                              Hi Expertie,

                              I have moved all my package level variables to spec also to make it stateless.
                              Now also i am getting same issue.