Friday, March 30, 2007

Imp and ORA-02430 cannot enable constraint

There is nothing particularly difficult in finding the cause of this message, but it's somewhat funny to read the action paragraph in the official Oracle Database Error Message:

ORA-02430: cannot enable constraint (string) - no such constraint
Cause: the named constraint does not exist for this table.
Action: Obvious
Obviously, isn't it?

At any rate, as i said, it's not so difficult to understand the reason of the error message, but to find the real reason of the problem may require just a little longer.

Scenario:

I was running IMP for loading a schema dump.
It terminated with warnings.
In the list of errors i encountered, i found the error above and i lazily looked up the error message in the official error message book.

Why doesn't exist? It's supposed to be *in the dump*, i mean, i am not executing this statement manually, so the name of the constraint i am sure is correct.

So, if the name isn't the problem, it must be the constraint itself that is not present.
Why?
The constraint DDL statement must be good, i am importing an empty schema, without data, so it cannot be because of a referential violation in the data that caused my not deferrable initially not deferred constraint to fail.

Then i must conclude it's a problem with the underlying objects, either the table upon which the referential constraint is being built or with the table column(s) being referred to.

This means to go back to the import log and dig out previous error messages associated to those objects.
Eventually i got the real one:
the table upon which the constraint is built was not created because its DDL statement was referencing a nonexistent tablespace.
As a result, the constraint was not built, so, obviously, it could not be enabled by imp.exe at a later time.

This is just to show that what is obvious for Oracle, can be less obvious, albeit not dramatically difficult, to spot for the DBA.



Translated messages:
ORA-02430: impossibile abilitare vincolo - non esiste tale vincolo
ORA-02430: no se puede activar la restricción - no existe tal restricción
ORA-02430: no es pot activar la restricció - no existeix
ORA-02430: impossible désactiver contrainte - pas de telle contrainte
ORA-02430: Constraint nicht aktivierbar - Constraint ist nicht vorhanden
ORA-02430: δεν μπορεί να γίνει ενεργοποίηση περιορισμού - δεν υπάρχει τέτοιος περιορισμός
ORA-02430: begrænsningen kan ikke aktiveres - ingen sådan begrænsning
ORA-02430: Kan inte aktivera begränsningen - den saknas
ORA-02430: kan ikke aktivere skranken - skranken finnes ikke
ORA-02430: rajoitetta ei voi ottaa käyttöön - tällaista rajoitetta ei ole
ORA-02430: a(z) megszorítás nem engedélyezhető - nem létezik
ORA-02430: nu se poate activa constrângerea - nu există o astfel de restricţie
ORA-02430: Kan beperking niet activeren - beperking bestaat niet.
ORA-02430: não é possível ativar a restrição - esta restrição não existe
ORA-02430: não é possível activar restrição - não existe esta restrição
ORA-02430: невозможно разблокировать ограничение - нет такого ограничения
ORA-02430: nelze aktivovat omezení - toto omezení neexistuje
ORA-02430: nemožno aktivovať obmedzenie - také obmedzenie neexistuje
ORA-02430: nie można uaktywnić więzów - nie ma takich więzów
ORA-02430: kısıtlama etkinleştirilemez - böyle bir kısıtlama yok

No comments:

yes you can!

Two great ways to help us out with a minimal effort. Click on the Google Plus +1 button above or...
We appreciate your support!

latest articles