Oracle Server - Enterprise Edition - Version 10.2.0.1 to 10.2.0.5 [Release 10.2] Information in this document applies to any platform.
Symptoms
The utl_recomp package errors out trying to compile a schema.
SQL> exec sys.utl_recomp.recomp_serial('SCOTT');
BEGIN SYS.UTL_RECOMP.RECOMP_SERIAL('SCOTT'); 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_serial('SCOTT');
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_serial('SCOTT');
Bug:9881143 is fixed in 11g, and there is a one-off patch available for 10.2.0.5 through Patch:12560454.
References
BUG:9881143 - UTL_RECOMP.RECOMP_SERIAL RESULTS IN ORA-00955 NOTE:12560454.8 - Bug 12560454 - ORA-955 from UTL_RECOMP