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.  

Constraints (Part 3) - More About Unique Constraints

Go to part 2

This example, tested on an Oracle 11 database, shows a different way to set up a unique constraint. As before, a table is created:

SQL> create table my_table

  2  (my_column number)

  3  /

Table created.

SQL>

Then a check is carried out to ensure there is no constraint or unique index called MY_UK:

SQL> select owner, constraint_name, constraint_type, status

  2  from dba_constraints

  3  where constraint_name = 'MY_UK'

  4  /

no rows selected

SQL> select owner, index_name, uniqueness

  2  from dba_indexes

  3  where index_name = 'MY_UK'

  4  /

no rows selected

SQL>

A constraint is added to the table:

SQL> alter table my_table

  2  add constraint my_uk

  3  unique (my_column)

  4  /

Table altered.

SQL>

This time the new constraint is included in DBA_CONSTRAINTS. Note the CONSTRAINT_TYPE of U, as this is a unique key constraint:

SQL> select owner, constraint_name, constraint_type, status

  2  from dba_constraints

  3  where constraint_name = 'MY_UK'

  4  /

OWNER      CONSTRAINT_NAME CONSTRAINT_TYPE STATUS

---------- --------------- --------------- --------

ANDREW     MY_UK           U               ENABLED

SQL>

There is also an index associated with the constraint:

SQL> select owner, index_name, uniqueness

  2  from dba_indexes

  3  where index_name = 'MY_UK'

  4  /

OWNER      INDEX_NAME UNIQUENESS

---------- ---------- ----------

ANDREW     MY_UK      UNIQUE

SQL>

A row is added to the table:

SQL> insert into my_table values (1)

  2  /

1 row created.

SQL>

And the unique constraint can then be checked by attempting to add the same row again:

SQL> insert into my_table values (1)

  2  /

insert into my_table values (1)

*

ERROR at line 1:

ORA-00001: unique constraint (ANDREW.MY_UK) violated

SQL> select * from my_table

  2  /

 MY_COLUMN

----------

         1

The constraint is disabled:

SQL> alter table my_table

  2  disable constraint my_uk

  3  /

Table altered.

SQL>

This changes its status:

SQL> select owner, constraint_name, constraint_type, status

  2  from dba_constraints

  3  where constraint_name = 'MY_UK'

  4  /

OWNER      CONSTRAINT_NAME CONSTRAINT_TYPE STATUS

---------- --------------- --------------- --------

ANDREW     MY_UK           U               DISABLED

SQL>

And the index disappears:

SQL> select owner, index_name, uniqueness

  2  from dba_indexes

  3  where index_name = 'MY_UK'

  4  /

no rows selected

SQL>

It is then possible to add duplicate data to the table:

SQL> insert into my_table values (1)

  2  /

1 row created.

SQL> select * from my_table

  2  /

 MY_COLUMN

----------

         1

         1

SQL>

Go to part 4

Constraints (Part 2) - ORA-00001 and ORA-02431

Go to part 1

The example below was checked on Oracle 9, 10 and 11. First a table was created:

SQL> create table my_table

  2  (my_column number)

  3  /

Table created.

SQL>

The number of constraints in the database was checked:

SQL> select count(*) from dba_constraints

  2  /

  COUNT(*)

----------

      7658

SQL>

A unique index was added to ensure that no value appeared more than once in my_column:

SQL> create unique index my_uk

  2  on my_table (my_column)

  3  /

Index created.

SQL>

The number of constraints was checked again but it had not changed. This suggested that creating the unique index had not added an extra constraint to the database:

SQL> select count(*) from dba_constraints

  2  /

  COUNT(*)

----------

      7658

SQL>

An attempt was made to insert duplicate values into my_column:

SQL> insert into my_table values (1)

  2  /

1 row created.

SQL>

The attempt failed but the error message displayed suggested that creating the unique index had, in fact, added a constraint:

SQL> insert into my_table values (1)

  2  /

insert into my_table values (1)

*

ERROR at line 1:

ORA-00001: unique constraint (ANDREW.MY_UK) violated

SQL>

The table was queried to check that the unique index had worked as expected:

SQL> select * from my_table

  2  /

 MY_COLUMN

----------

         1

SQL>

If this happens and you decide that you genuinely need to allow duplicates in my_column, how would you do this? You cannot disable the constraint because it does not exist:

SQL> alter table my_table

  2  disable constraint my_uk

  3  /

alter table my_table

*

ERROR at line 1:

ORA-02431: cannot disable constraint (MY_UK) - no

such constraint

SQL>

Although this constraint does not appear in DBA_CONSTRAINTS, you can see what is enforcing it by looking for an index with that name in DBA_INDEXES. It will have UNIQUENESS set to UNIQUE:

SQL> select uniqueness from dba_indexes

  2  where index_name = 'MY_UK'

  3  /

UNIQUENES

---------

UNIQUE

SQL>

So, to allow duplicate values in my_column, you just need to drop the unique index (and replace it with an ordinary one if you find that an index is still required).

Go to part 3