Q:

Wierd behavior with unusable index - oracle

I do have a big migration process where there is a scenario in which its trying to insert a row into a table which has a primary key constraint and unique index for it. Before inserting the constraint is disabled (checked it with SQL developer) and the index is marked unusable (checked it again with SQL developr). But still when inserting its giving an error
01502. 00000 -  "index '%s.%s' or partition of such index is in unusable state"

So I wanted to try out executing the query manually from SQL developer and still the same result. I even explicitly tried to set the session (and system) skip unusable index like this
BEGIN
EXECUTE IMMEDIATE 'ALTER SESSION SET SKIP_UNUSABLE_INDEXES = TRUE';
single row insert line
END;

But still the same error. My understanding is that if constraints are disabled; indexes are unusable and we skip the unsusable index we should be able to insert. Is it correct? Am i missing something very trivial here? Any suggestions/help appreciated.
PS: Oracle 11.2.0.1.0

A:

If the index is not UNIQUE, you can set SKIP_UNUSABLE_INDEXES = true and it should work.
However, in your case, as the index is unique, I dont think you can do this.
http://docs.oracle.com/cd/B28359_01/server.111/b28320/initparams229.htm

