Thanks for the question, Sarayu.

Asked: August 06, 2009 - 4:56 pm UTC

Last updated: February 26, 2021 - 3:48 am UTC

Version: 10.2.0.4

Viewed 100K+ times! This question is

You Asked

Hi Tom,

In our database, few indexes became unusable.
We reviewed metalink 1054736.6 and to my knowledge none of the
points listed in that metalink happened in our database.
Also the table is not a partitioned table.
There was no maintenance operation happened on the table.

Can you please let me know , under what conditions (other than
the one described in the metalink above) an index would be
unusable? A simple rebuild of the index made it valid. These indexes are just simple indexes.

Thank You.

Sarayu K.S.

and Tom said...

.. There was no maintenance operation happened on the table. ...

yes, there was - on that table or on that index.

I would guess that someone made the indexes unusable (alter index I unusable)

or maybe they did a direct path load that left the index unusable.

ops$tkyte%ORA10GR2> create table emp as select * from scott.emp;

Table created.

ops$tkyte%ORA10GR2> alter table emp add constraint emp_pk primary key(empno);

Table altered.

ops$tkyte%ORA10GR2> select status from user_indexes where index_name = 'EMP_PK';

STATUS
--------
VALID

ops$tkyte%ORA10GR2> !sqlldr / emp direct=y

SQL*Loader: Release 10.2.0.4.0 - Production on Fri Aug 7 13:58:08 2009

Copyright (c) 1982, 2007, Oracle.  All rights reserved.


Load completed - logical record count 14.

ops$tkyte%ORA10GR2> select status from user_indexes where index_name = 'EMP_PK';

STATUS
--------
UNUSABLE

that one won't rebuild until you clean up the duplicate values.

But in short, you did some operation on the table or index, or one of your coworkers manually set the index unusable, or you did some sort of direct path operation.

I'd guess, if I had to, that someone decided to "reorganize" a table, that'll do it too.

ops$tkyte%ORA10GR2> create table emp as select * from scott.emp;

Table created.

ops$tkyte%ORA10GR2> alter table emp add constraint emp_pk primary key(empno);

Table altered.

ops$tkyte%ORA10GR2> select status from user_indexes where index_name = 'EMP_PK';

STATUS
--------
VALID

ops$tkyte%ORA10GR2> alter table emp move;

Table altered.

ops$tkyte%ORA10GR2> select status from user_indexes where index_name = 'EMP_PK';

STATUS
--------
UNUSABLE

ops$tkyte%ORA10GR2> select object_id, data_object_id from user_objects where object_name = 'EMP';

 OBJECT_ID DATA_OBJECT_ID
---------- --------------
    177374         177376

if you use that last query against some of the affected tables and see that the data object_id <> object_id - then there is a good chance this is a correct guess.

Rating

(12 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Alexander, August 10, 2009 - 12:22 pm UTC

Tom, why is it that indexes become unusable after direct path loads? Isn't it true that modifications(redo) to indexes are not able to be bypassed, so shouldn't they remain unaffected?

August 13, 2009 - 8:47 am UTC

they do not in general.

It is one way they COULD become unusable.

If you have a unique index and you duplicate data during a sqlldr direct path load - the unique indexes are left in direct load state - you have to clean your data before we can re-enable them.

How to fix such cases when the index is a PK and has dependencies?

Brajesh, October 22, 2009 - 3:27 pm UTC

Hi Tom,

Your detailed analysis on Oracle issues serves a great cause to all the Oracle users communities. I have a similar issue and need your view on that. I have a table which primary key is in unusable state and duplicate records are created by some user (may be through direct upload: don't know what he did). Now it has come to me to fix the problem. But the problem is that I cann't update/delete those duplicates as the index is unusable. At the same time, I don't want to drop the constraint and recreate the index( it has tons of dependent tables). Is there any way to fix this without dropping the index and recreating it?
And how it is possible that Oracle allowed that to happen?
Thanks,
Brajesh

October 23, 2009 - 2:00 pm UTC

... (may be through direct upload: don't know what he did) ...

I do, they direct path loaded data, it had duplicates and the index was left in a direct load state as it is documented to be.

... But the problem is that I cann't update/delete
those duplicates as the index is unusable....

the index is broken, it HAS to be recreated, it is unusable, there are rows in the base table it doesn't point to, you have no choice - that direct path load broke that index, it is not fixable, it will be rebuilt.

... And how it is possible that Oracle allowed that to happen?...

it is a documented process of a direct path load, direct path loads must be used with care as they can bypass redo, they do not fire triggers and unique indexes can be left in what is known as a direct load state if they are not unique after the direct path operation.

http://docs.oracle.com/docs/cd/B19306_01/server.102/b14215/ldr_control_file.htm#sthref770

http://docs.oracle.com/docs/cd/B19306_01/server.102/b14215/ldr_modes.htm#sthref1486

http://docs.oracle.com/docs/cd/B19306_01/server.102/b14215/ldr_modes.htm#sthref1580

some facts on my last question

Brajesh, October 22, 2009 - 3:38 pm UTC

SQL> update xyz set org_id = 999 where org_id=4010 and rownum <2;
update xyz set org_id = 999 where org_id=4010 and rownum <2
*
ERROR at line 1:
ORA-01502: index 'ABC.PK_ORG_ID' or partition of such index is in unusable state

SQL> delete xyz where org_id=4010 and rownum <2;
delete xyz where org_id=4010 and rownum <2
*
ERROR at line 1:
ORA-01502: index 'ABC.XYZ' or partition of such index is in unusable state

SQL> alter table XYZ disable primary key;
alter table XYZ disable primary key
*
ERROR at line 1:
ORA-02297: cannot disable constraint (ABC.PK_ORG_ID) - dependencies exist


This is a primary table which is referred by 62 child tables. I cann't insert any record through SQl*Plus. Is it expected behavior from Oracle to allow insert through direct LOAD even it violates the uniqueness of a primary key?

Direct Path Load

abhi, July 01, 2013 - 7:32 am UTC

Hi Tom

I have a table emp(empid,name) with empid as Primary Key.

Using SQLLDR direct = y, We can insert duplicate empids in the table as you already described.

Why can't we Insert duplicate empids using

Insert /*+ append */

I believe it is also a way of direct path loading?

Thanks
Abhi

July 01, 2013 - 9:25 pm UTC

because the SQL statement takes the database from one consistent state to the next and observes all integrity constraints.

a direct path load just formats database blocks, writes them to disk, and then maintains the index for the primary key *after* the load of table data is complete. We don't discover until afterwards - after the load is complete and done - that there are duplicates.

it is just the way it was designed and documented to work, the direct path load can leave an index in "direct load" state and leave the unique constraint disabled.

Direct path load using Insert /+ Append /

Abhi, July 02, 2013 - 1:44 pm UTC

Thanks for the reply, Tom

But my question really was--

If we use /*+ Append */ while Insert, it is also direct path loading.

So why (Insert /*+ Append */ ...) gives error if we try to insert duplicate records on a column with Primary key or Unique key constraint, While SQLLDR Direct =y, permits it.

Both are direct path loading.

What is the difference between direct path loading Using SQL Loader and Insert /*+ append*/ ?

--------------------------
On a separate note, if we do not specify direct=y in case of SQLLDR, is it not direct path loading? Is it conventional loading?

Thanks
Abhi

Another user

A reader, July 18, 2013 - 2:19 pm UTC

just waiting for your explanation regarding above OP question.

Thanks for your time.

Regard's

July 18, 2013 - 5:41 pm UTC

if you mean the review one up from you (no idea what "above OP question" means) - the reason is

one is SQL, insert /*+ append */ is sql and sql statements either entirely succeed or entirely fail. The index maintenance is part of the insert /*+ append */ and nothing is committed until the user types "commit"

a sqlldr direct path load reads input data, writes output data and is constantly 'checkpointing' itself. it doesn't have the transactional consistency demanded by sql. It is a direct path load, it is just different from a direct path insert.

hence, by the time sqlldr gets around to maintaining the index - the table data is already loaded, committed, done. the index is then updated and the duplicates are discovered. sqlldr then leaves the index in a direct load state and reports the issue.

A reader, July 19, 2013 - 4:24 pm UTC

aah get it thanks for clarification :)

Very useful info

Yathiraj, January 17, 2014 - 9:01 am UTC

Hi Tom,
From your explanation, I understood why the indexes become unusable. In my case, Yes, I did a direct load which caused the unique index to become unusable. How do we make is usable? It's not allowing me to delete the rows or truncate the table (due to foreign key references) and it can't be rebuilt as duplicate rows exists.

Very helpful if you can please help me with this.

Thanks,
Yathiraj

unable to delete duplicates inserted by sqlldr

varun bahl, February 24, 2021 - 7:37 am UTC

Hi.
Can you suggest how can we delete the duplicate records inserted by sqlldr? We receive an error while deleting the duplicate records.

ORA-01502: index '*****.****_UIDX' or partition of such index is in unusable state

February 24, 2021 - 8:58 am UTC

You need to rebuild the unusable index before you can change data in the table.

unable to delete duplicates inserted by sqlldr

varun bahl, February 25, 2021 - 8:03 am UTC

Hi Chris,

The Unusable index cannot be rebuild unless we delete the duplicate data. Index rebuild gives the below error(ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found):

ALTER INDEX ***_UIDX REBUILD PARTITION PXXXX PARALLEL;
ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found

We are unable to delete the duplicate records because the index is in unusable state and we cannot rebuild index because there are duplicates....It seems to be a vicious circle..

February 25, 2021 - 8:43 am UTC

Then you need to remove the duplicate data!

Drop the constraint, delete the duplicates with something like:

delete ...
where  rowid not in (
  select min(rowid)
  from   ...
  group  by cols, in_the, unique_index
)

Then recreate the constraint/index.

For more on finding and removing duplicates, see:

https://blogs.oracle.com/sql/how-to-find-and-delete-duplicate-rows-with-sql

unable to delete duplicates inserted by sqlldr

A reader, February 25, 2021 - 8:23 am UTC


You can drop/disable the constraints and clean the data. Once done create the constraint again.

unable to delete duplicates inserted by sqlldr

varun bahl, February 26, 2021 - 3:02 am UTC

Thanks for your help.

I had to drop the entire index which was present on 1 billion records in a partitioned table. It didn't allow me to drop the single index partition which had the duplicate records. other option I had was to drop the table partition itself which could drop the index partition.

There was no PK constraint present. Disabling or dropping the constraint doesn't work as doing so doesn't drops/impacts the underlying unique index. So the only options left was
1. Drop the entire index on the partitioned table.
2. Drop the table partition which has duplicates. This will require to reload the data.

February 26, 2021 - 3:48 am UTC

If you don't think there will be many dups, then check out:

alter table ... enable constraint ... exceptions into exceptions;

Live SQL

Share and learn SQL and PL/SQL; free access to the latest version of Oracle Database!

Dev Gym

Classes, workouts and quizzes on Oracle Database technologies. Expertise through exercise!

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

How to fix such cases when the index is a PK and has dependencies?

some facts on my last question

Direct Path Load

Direct path load using Insert /+ Append /

Another user

Very useful info

unable to delete duplicates inserted by sqlldr

unable to delete duplicates inserted by sqlldr

unable to delete duplicates inserted by sqlldr

unable to delete duplicates inserted by sqlldr

More to Explore

Live SQL

Dev Gym

Why indexes become unusable?

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

How to fix such cases when the index is a PK and has dependencies?

some facts on my last question

Direct Path Load

Direct path load using Insert /*+ Append */

Another user

Very useful info

unable to delete duplicates inserted by sqlldr

unable to delete duplicates inserted by sqlldr

unable to delete duplicates inserted by sqlldr

unable to delete duplicates inserted by sqlldr

More to Explore

Live SQL

Dev Gym

Direct path load using Insert /+ Append /