Re: ORA-04068 : always returns
Date: Tue, 03 Sep 2002 09:53:52 -0700
Message-ID: <3D74E920.DE51C816_at_hotmail.com>
Hi, try this.
Causes of ORA-4068 Errors
- 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:
- 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
- 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>
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:74149.1] entitled "Invoker Rights versus Definer
Rights
in Oracle 8i".
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:99913.1] DBMS_RANDOM fails with Errors Referencing 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 [NOTE:2096539.6] RECOMPILE THE PL/SQL STORED OBJECTS Related Errors
ORA-4061 ORA-4063 ORA-4065 ORA-4067 ORA-4068 ORA-4088 ORA-6508 ORA-6509 ORA-6568
ORA-6512 Regards,
Marcelo Muzilli
dbeelpre wrote:
> > Please help because this is running in production-environnement and give the > users a lot of problems. > Please help because this is running in production-environnement and give the > users a lot of problems. > Please help because this is running in production-environnement and give the > users a lot of problems. > > Hi, > > from a form in database X I'm calling a procedure from another database Y > (both Oracle7) > > Since yesterday, in that form I get ORA-04068 : existing state of packeges > has been discarded. > > Everything worked fine for more than 2 years. > > I recompiled the procedure (and all the other procedures) but nothing > changed. > > I finaly dropped that procedure and made it again, new grant, new alias but > that form still gives me ORA-04068. > > Everywhere I look on the internet, the solution is 'try it again and the > problem will be solved'. > > I tryed the things above. > > Has someone another idea? > > Please help because this is running in production-environnement and give the > users a lot of problems. > > Thanks > > DirkReceived on Tue Sep 03 2002 - 18:53:52 CEST