Ora-955 Error Running Utl_Recomp.Recomp_Serial :

Error:

Ora-955 Error Running Utl_Recomp.Recomp_Serial [ID 1274504.1]            To Bottom          

The utl_recomp package errors out trying to compile a schema.

SQL> exec sys.utl_recomp.recomp_serial('SCOTT');

(OR)

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

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.

SQL> alter session set events '955 trace name errorstack level 3';

SQL> exec sys.utl_recomp.recomp_serial('SCOTT');

Looking in the trace file will show the problem index.

*** 2012-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

SQL> drop index utl_recomp_comp_idx1;

SQL> exec sys.utl_recomp.recomp_serial('SCOTT');

Workaround:

 Drop index utl_recomp_comp_idx1 before attempting the recompile

++++++++++++++++++++++++