ORA-00604 and ORA-00001

This happened in an Oracle 11.1.0.6.0 database:

 

A developer reported problems when running a CREATE OR REPLACE TYPE statement in a development database. It was failing with an ORA-00604 followed by an ORA-00001. These messages could be seen again and again in the alert log:

ORA-00604: error occurred at recursive SQL level 3

ORA-00001: unique constraint (SYS.I_OBJ1) violated

I could see no reason for this so I did some research on the Internet then suggested that the data dictionary might be corrupted. This was quite possible as a number of people have the SYSTEM password for the database in question so somebody could easily have done something silly with it.

 

A second colleague reported the same errors when compiling packages and dropping and creating global temporary tables. Another DBA looked at the problem this time. He tried to recompile all invalid objects in the database and noticed that the compilation of one procedure displayed several errors before failing with a core dump. He amended the procedure a bit at a time to fix the errors until the compilation no longer caused a core dump. He then purged the recycle bin and asked the 2^nd developer to try again. He reported that the problem had gone away.

 

I reran the CREATE OR REPLACE TYPE statement provided by the 1^st developer and it worked too.