Oracle Error Codes 4068
Contents |
July 25, 2003 - 3:43 am UTC Category: Database � Version: 8.1.7.0.0 Whilst you are here, check out some content from the AskTom team: PL/SQL Brain ora-4061 Teaser: How can BULK COLLECT be used? Latest Followup You Asked ora-4065 I know that package variables will cause ORA-4068 error messages when another user tries to re-compile the package ora-04061 solution body that is already in use. I know why I am getting these errors, I need to know how I can detect these errors before the user compiles a package
Ora-04068 Solution
body. -- For example, a user creates a package: session1 > l 1 create or replace package foo 2 is 3 function bar return varchar2; 4* end foo; session1 > / Package created. Then, in the same session, the user creates the package body: session1 > l 1 create or replace package body foo 2 is 3 v2 varchar2(80); 4 ora 04061 ora 04065 ora 06508 function bar return varchar2 5 is 6 ver varchar2(80); 7 begin 8 v2:='this is a package variable.'; 9 ver := v2; 10 return ver; 11 end bar; 12* end foo; session1 > / Package body created. Then the user executes the package: session1 > set serveroutput on session1 > exec dbms_output.put_line(foo.bar); this is a package variable. PL/SQL procedure successfully completed. Now another sqlplus session is started in window#2 as the same user and he executes the package: session2 > set serveroutput on session2 > exec dbms_output.put_line(foo.bar); this is a package variable. PL/SQL procedure successfully completed. Now, the user needs to recompile the body of foo. If he recompiles the package in session1, an ORA-4068 error will be generated in session2. Before the first session recompiles the package body, I want to be able to find out if he will generate an ORA-4068 error for any other users using that package. and we said... Run catblock.sql found in $ORACLE_HOME/rdbms/admin when logged in as SYS using svrmgrl (if you haven't already). That'll give you a view DBA_DDL_LOCKS. Now
Problem(Abstract) What are the causes of ORA 4068 errors? Symptom PART: DCS 6.2 SP3 DCSWhat are the causes of ORA 4068 errors? Resolving the problem Problem ora-4068 Description-------------------You receive the following error from user-written and Oracle packages: ORA-04068 existing state of packages%s%s%s has been discardedCauses 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 https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:358013111598 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, ...), http://www.ibm.com/support/docview.wss?uid=swg21535552 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 = '
Message-ID: <1134731322.538933.145790@g47g2000cwa.googlegroups.com> Hi all. Many of us know the issue: You change http://www.orafaq.com/usenet/comp.databases.oracle.misc/2005/12/16/0328.htm a stateful package's body (i.e., having package variables) and recompile it; http://www.dba-oracle.com/sf_ora_04068_existing_state_of_packages_string_has_been_discarded.htm when using this package in the same session where it was instantiated, you will repeatedly receive the ORA-04061/ORA-04068 error chain ("existing state of packages has been discarded" resp. "existing state of package body "
SQL TuningSecurityOracle UNIXOracle LinuxMonitoringRemote supportRemote plansRemote servicesApplication Server ApplicationsOracle FormsOracle PortalApp UpgradesSQL ServerOracle ConceptsSoftware SupportRemote Support Development Implementation Consulting StaffConsulting PricesHelp Wanted! Oracle PostersOracle Books Oracle Scripts Ion Excel-DB Don Burleson Blog
ORA-04068: existing state of packages has been discarded tips Oracle Error Tips by Burleson Consulting The Oracle oerr utility notes this about ORA-04068 error: ORA-04068: existing state of packages string has been discardedCause: One of errors 4060 - 4067 when attempt to execute a stored procedure.Action: Try again after proper re-initialization of any application's state. ORA-04068 can be caused by a variety of initiating events. In some cases with RAC, the ORA-04068 error can be suppressed with this command: alter system set "_disable_fast_validate"=true scope=both; If a remote dependent object has been altered through a DDL statement then invoking the procedure/package can result ORA-04068: existing state of packages has been discarded . voking the procedure can then cause the ORA-04068. SQL expert Laurent Schneider has noted that pragma SERIALLY_REUSABLE can be used in some situations to avoid the ORA-04068. Pragma SERIALLY_REUSABLE is an indication that the package state is needed only for one call to the server, but having a particular package state for that one call can help avoid ORA-04068. Burleson is the American Team Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals. Feel free to ask questions on our Oracle forum. Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications. Errata? Oracle technology is changing and we strive to update our BC Oracle support information. If you find an error or have a suggestion for improving our content, we would appreciate your feedback. Just e-mail: and include the URL for the page. Burleson Consulting The Oracle of Database Support Oracle Performance Tuning Remote DBA Services Copyright © 1996 - 2016 All rights reserved by Burleson Oracle is the registered trademark of Oracle Corporation. ��