1

I have table where it's primary key index got unusable due to partition merge ( I may have done it wrong).

select INDEX_NAME, STATUS from ALL_INDEXES WHERE TABLE_NAME = TRANSACTION' AND INDEX_NAME = 'PK_TRAN';

Status from above query is N/A (unusable)

Then I tried to drop the index

drop index PK_TRAN;

resulted following error.

SQL Error: ORA-02429: cannot drop index used for enforcement of unique/primary key

Then i tried to drop the constraint

alter table TRANSACTION drop constraint PK_TRAN;

resulted following error,

SQL Error: ORA-02273: this unique/primary key is referenced by some foreign keys

Then i tried to drop the problematic reference constraints

select CONSTRAINT_NAME from all_constraints where constraint_type='R' and r_constraint_name='PK_TRAN';

alter table A drop constraint FK_1;

which resulted following error.

SQL Error: ORA-14650: operation not supported for reference-partitioned tables

How can i make transaction table's pk index valid(usable). Currently operation on this table fails with

ORA-01502: index 'PK_TRAN' or partition of such index is in unusable state
  • N/A is not necessarily unusable, it means the status is not available.This must be a partition index and you looked at the wrong view. What is the result of select INDEX_NAME,PARTITION_NAME,STATUS from user_ind_partitions where index_name='PK_TRAN';? Try to alter index PK_TRAN rebuild; – JSapkota Mar 6 '17 at 10:58
  • Above query returns multiple records. Each for PARTITION_NAME. All of them have UNUSABLE state. – Viraj Mar 6 '17 at 11:05
2

01502, 00000, "index '%s.%s' or partition of such index is in unusable state"

MERGE: 1489 RENUMBERED TO 1502

Cause: An attempt has been made to access an index or index partition that has been marked unusable by a direct load or by a DDL operation.

Action: DROP the specified index, or REBUILD the specified index, or REBUILD the unusable index partition.

Now, you have to rebuild index(es) according to their type, Global or Local.

You can rebuild global index partitions in two ways:

  • Rebuild each partition by issuing the ALTER INDEX REBUILD PARTITION statement (you can run the rebuilds concurrently).

  • Drop the entire global index and re-create it. This method is more efficient because the table is scanned only one time.

    Rebuild local indexes using either ALTER INDEX or ALTER TABLE as follows:

  • ALTER INDEX REBUILD PARTITION/SUBPARTITION

    This statement rebuilds an index partition or subpartition unconditionally.

  • ALTER TABLE MODIFY PARTITION/SUBPARTITION REBUILD UNUSABLE LOCAL INDEXES

    This statement finds all of the unusable indexes for the given table partition or subpartition and rebuilds them. It only rebuilds an index partition if it has been marked UNUSABLE.

  • Documentation:About Rebuilding Index Partitions

    • Issue was resolved using given information. Thanks JSapkota. Queries as follows select p.index_name, p.partition_name,p.status from dba_ind_partitions p, dba_indexes i where i.index_name=p.index_name and table_name='TRANSACTION' to get the index and partitions. To rebuild each partition ALTER INDEX PK_TRAN REBUILD PARTITION {partitionName} – Viraj Mar 7 '17 at 7:54

    Your Answer

    By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

    Not the answer you're looking for? Browse other questions tagged or ask your own question.