Thursday, February 23, 2012

sys.utl_recomp.recomp_parallel(20); is failing with "ORA-00955: name is already used by an existing object"

Issue
The utl_recomp package errors out trying to compile invalids


SQL> exec sys.utl_recomp.recomp_parallel(20);


BEGIN SYS.UTL_RECOMP.RECOMP_PARALLEL;END;
*
ERROR at line 1:
ORA-00955: name is already used by an existing object
ORA-06512: at "SYS.UTL_RECOMP", line 662
ORA-06512: at "SYS.UTL_RECOMP", line 715
ORA-06512: at line 1


Cause
Bug:9881143 where an index is left from a previous failed execution of utl_recomp.  The following commands can be used to generate a trace file to verify.

alter session set events '955 trace name errorstack level 3';
exec sys.utl_recomp.recomp_parallel(20);

Looking in the trace file will show the problem index.

*** 2010-12-16 12:42:24.026
ksedmp: internal or fatal error
ORA-00955: name is already used by an existing object
Current SQL statement for this session:
CREATE INDEX utl_recomp_comp_idx1 ON utl_recomp_compiled(obj#) NOPARALLEL

Solution

Drop the index utl_recomp_comp_idx1 left by utl_recomp and then rerun the package.

sqlplus / as sysdba
drop index utl_recomp_comp_idx1;
exec sys.utl_recomp.recomp_parallel(20);

Bug:9881143 is fixed in 11.1.0.6.


Ref:Error Running Utl_Recomp.Recomp_Serial [ID 1274504.1]

2 comments:

Anonymous said...

Hi!
Where is located the trace file?

Thanks!

Balaganesh Gembali said...

udump

Enabling SSL or TLS in Oracle E-Business Suite Release 12.2

Enabling TLS in Oracle Apps R12.2 Here  we would be looking at the detailed steps for Enabling TLS in Oracle Apps R12.2 Introduction: ...