This combination of aprimary key policed by a non-unique unusable index on table direct path loaded raising error ORA-26026 has occurred again following a recent upgrade from 18.104.22.168 to 22.214.171.124
I know that many authors like Randolph Geist and Richard Foote has already wrote something about this subject. I, however, for the sake of my proper documentation, decided to summarise this issue in my blog.
The story comes when a developer sends me an e-mail saying that he has observed the following error in one of his overnight batch jobs in one of the TEST databases:
ORA-26026 : unique index xxx.t1_non_unique_idx initially in unusable state
Notice how the error message is pointing to a unique index error on a non-unique index. That was, for me, the first clue to what is really happening in the developer situation. The name of the developer index in the original error text was not so suggestive about the uniqueness of the index as the one I used in the above error message. The developer batch job was accomplishing the following steps:
- disable all non-unique indexes on the target table
- parallel direct path load into the target table
The developer says that his job was running quite smoothly in 126.96.36.199 and started failing because of the ORA-26026 following a fresh upgrade to 188.8.131.52. Here’s below the developer set-up you can use and play with at will:
create table t1 as select rownum n1 ,trunc((rownum-1/3)) n2 ,mod(rownum,10) n3 from dual connect by level <= 1e3; create index t1_non_unique_idx on t1(n1,n2); alter table t1 add constraint t1_pk primary key (n1) using index; create unique index t1_pk on t1(n1); select index_name, uniqueness from user_indexes where table_name = 'T1'; INDEX_NAME UNIQUENES ------------------------------ --------- T1_PK UNIQUE T1_NON_UNIQUE_IDX NONUNIQUE select constraint_name ,index_name from user_constraints where table_name = 'T1' and constraint_type = 'P'; CONSTRAINT INDEX_NAME ---------- --------------------- T1_PK T1_NON_UNIQUE_IDX
With this set-up in place the primary key constraint is policed by the non-unique index t1_non_unique_idx. The batch continues then by disabling all non-unique indexes and direct path load into the target table. Something resembling to the following piece of SQL code:
create table t2 as select * from t1; truncate table t1; alter index t1_non_unique_idx unusable;
Execute the following insert/select piece of code in 184.108.40.206 and you will not encounter any error:
insert /*+ append */ into t1 select * from t2;
Upgrade to 220.127.116.11 (or 12c) and you will immediately hit the ORA-26026:
ERROR at line 1: ORA-26026: unique index C##MHOURI.T1_NON_UNIQUE_IDX initially in unusable state
In the developer case, despite the existence of a perfect unique index to cover the Primary key, this constraint was enforced via a non-unique index. It seems that the developer has created the T1_PK index after he has created the non-unique index and the primary key constraint.
To solve this issue all what I did is to change the index enforcing the primary key as follows:
alter table t1 modify constraint t1_pk using index t1_pk; select constraint_name ,index_name from user_constraints where table_name = 'T1' and constraint_type = 'P'; CONSTRAINT INDEX_NAME ---------- ---------- T1_PK T1_PK insert /*+ append */ into t1 select * from t2; 1000 rows created.
Bottom line : when you are direct path loading data into a table after you have disabled its non-unique indexes be sure that your primary key is not enforced via one of those disabled non-unique indexes. Otherwise, depending on the Oracle release you are using, your insert will be rejected because of the ORA-26026 error.