Hi Chris,
As the error message suggests, your index has become unusable (either thru dataload or some ddl statements). I'll recreate a possible scenario for you. Please look at the execution sequence below:
-----------------------------------------------------------------------------
SQL> create table t ( x number, v varchar2(10) );
Table created.
SQL> create index t_n1 on t ( x ) ;
Index created.
SQL> insert into t (x, v) values ( 1, 'a' );
1 row created.
SQL> insert into t (x, v) values ( 2, 'b' );
1 row created.
SQL> commit;
Commit complete.
SQL> select * from t where x = 2;
X V
---------- ----------
2 b
SQL> alter index t_n1 unusable; -- something like this has happened to your index
Index altered.
SQL> select * from t where x = 2; -- u try to access an unusable index
select * from t where x = 2
*
ERROR at line 1:
ORA-01502: index 'C5489.T_N1' or partition of such index is in unusable state
SQL> alter index t_n1 rebuild; -- now rebuild the index
Index altered.
SQL> select * from t where x = 2;
X V
---------- ----------
2 b
SQL>
-----------------------------------------------------------------------------
You need to find out which index is being accessed and rebuild it. You may get the information about the status of the index as follows:
-----------------------------------------------------------------------------
SQL> select index_name, status from user_indexes where index_name = 'T_N1' ;
INDEX_NAME STATUS
------------------------------ --------
T_N1 VALID
SQL> alter index t_n1 unusable;
Index altered.
SQL> select index_name, status from user_indexes where index_name = 'T_N1' ;
INDEX_NAME STATUS
------------------------------ --------
T_N1 UNUSABLE
SQL>
-----------------------------------------------------------------------------
You may refer to some excellent information on indexes in the Oracle documentation - "Oracle 8i Administrator's guide" and "Designing and tuning for performance".
Hope that helps.
Cheers,
Prat
|