Troubleshooting
Problem
What are the causes of ORA 4068 errors?
Symptom
PART: DCS 6.2 SP3 DCS
What are the causes of ORA 4068 errors?
Cause
Resolving The Problem
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
-----------------------------------------------------------------------------<br
> 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>
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.
Historical Number
PRI49575
Product Synonym
[<p><b>]Fact[</b><p>];
Was this topic helpful?
Document Information
Modified date:
16 June 2018
UID
swg21535552