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

Oracle EBS integration with Oracle IDCS for SSO

Oracle EBS integration with Oracle IDCS for SSO Oracle EBS SSO? Why is it so important? Oracle E-Business Suite is a widely used application...