Skip to content
April 8, 2013 / doganay

ORA-04065: not executed, altered or dropped


Problem:

ORA-04065: not executed, altered or dropped


Solution:

1) compile your object

if that does not work:

2) purge the cursor(s) which use your object:

SQL> select address, hash_value, executions, loads, version_count, invalidations, parse_calls from v$sqlarea where sql_text = 'select ename from emp where empno=7900';

ADDRESS HASH_VALUE EXECUTIONS LOADS VERSION_COUNT INVALIDATIONS
---------------- ---------- ---------- ---------- ------------- -------------
PARSE_CALLS
-----------
0000000382E80750 1052545619 1 1 1 0
1

SQL> exec dbms_shared_pool.purge ('0000000382E80750,1052545619','C');

PL/SQL procedure successfully completed.

SQL> select address, hash_value, executions, loads, version_count, invalidations, parse_calls from v$sqlarea where sql_text = 'select ename from emp where empno=7900';

no rows selected

if that does not work:

3) kill session which access your object:

col object format a30
col username format a20
col SIDserialins format a14

set linesize 200
alter session set "_optimizer_cartesian_enabled"=false;
SELECT a.SID||','||s.serial#||',@'||s.inst_id SIDserialins, s.last_call_et, s.status, s.sql_hash_value,
s.username, s.sql_hash_value, a.owner || '.' || a.OBJECT OBJECT, s.lockwait,s.osuser
FROM gv$session s, gv$access a
WHERE s.SID = a.SID
and s.inst_id = a.inst_id
AND a.owner != 'SYS'
AND UPPER (SUBSTR (a.OBJECT, 1, 2)) != 'V$'
AND a.OBJECT = upper(trim('&object_name'));

alter session set "_optimizer_cartesian_enabled"=true;

if that does not work:

4) compile all objects that depends to your object:

set pages 1000
set lines 1000
col name format a50

accept p_obje prompt "object_name..: "

select 'alter '||decode(type,'PACKAGE BODY','PACKAGE',type), owner||'.'||name||decode(type,'PACKAGE BODY',' compile body;',' compile;') from dba_dependencies
where referenced_NAME=upper(trim('&&p_obje'))
order by 1;

if that does not work:

5) try this:

* Extract create script of your object.
* Drop your object.
* compile all objects that depends to your object (step 4)

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: