I have a partitioned table: SAMPLE_PARTITIONED_TBL with 60 partitions (no sub-paritions) based on the PERIOD_ID numeric field (Data set: 201001...201212.. and so on). This table has several local Indexes but the problem lies with the PK index for some reason. I have the same DDL in another schema and it works fine there. Not sure what tio look for to resolve this.

I am using the Informatica (ETL tool) to load data into this table. Before loading we do truncate partition table:


As the load starts I get the following error:

Message: Database errors occurred: 
ORA-01502: index 'owner_name.SAMPLE_PARTITIONED_TBL_PK' or 
partition of such index is in unusable state

Looking at the indexes's status...

SQL> select STATUS from all_indexes


Now after executing SQL> ALTER INDEX owner_name.SAMPLE_PARTITIONED_TBL_PK REBUILD; the local partitioned index returns to STATUS=VALID state the loading can continue without a problem.


As per @Mat's observation below, I am checking if the PK Index is locally partitioned:

SQL> select * from all_indexes where table_name = 'SAMPLE_PARTITIONED_TBL';

I see SAMPLE_PARTITIONED_TBL_PK and the other indexes in the list.

SQL> select * from  all_part_indexes where table_name = 'SAMPLE_PARTITIONED_TBL';

Here I see all the indexes with LOCALITY='LOCAL'; except SAMPLE_PARTITIONED_TBL_PK is missing in the result set thus confirming Mat's observation. :)


1 Answer 1


That's expected, most DDL operations on partitions will invalidate the indexes affected by the DDL. The ALTER TABLE docs state that on all relevant operations.

Specifically for truncate partition:

For each partition or subpartition truncated, Oracle Database also truncates corresponding local index partitions and subpartitions. If those index partitions or subpartitions are marked UNUSABLE, then the database truncates them and resets the UNUSABLE marker to VALID.

So local indexes are truncated and marked valid. Global indexes are invalidated unless you specifically request that they be maintained. (See further down.)
In your case, it looks like your primary key is not based on a local index – it appears in fact not to be partitioned, since you can't do an alter index ... rebuild on a partitioned index, you need to rebuild each partition. So the invalidation is expected.

To do that, you can add an UPDATE INDEXES / UPDATE GLOBAL INDEXES clause to your ALTER TABLE statement to have Oracle automatically maintain the indexes for you during the ALTER - See Updating Indexes Automatically. There are some limitations though, read the Considerations when Updating Indexes Automatically section carefully.

But that's not always desirable. Sometimes it's more efficient to rebuild the indexes after the load. In that case, your load process can often use the SKIP_UNUSABLE_INDEXES parameter (can be set at session level). (This parameter defaults to TRUE in 11gR2.)

  • Thanks Mat. Will work on resolving it.. and get back to you. +1 for the references :)
    – Kent Pawar
    Commented Dec 10, 2012 at 7:29
  • 1
    Updated: looks like your PK isn't locally partitioned (from the docs and from tests here).
    – Mat
    Commented Dec 10, 2012 at 9:34
  • Yes, I see the issue now.. Thanks again Mat! Just updated the question above confirming your observations.
    – Kent Pawar
    Commented Dec 10, 2012 at 11:08
  • 1
    I just reproduced your scenario on a database here since after thinking about it more, it seemed strange that the local indexes would be invalidated for a truncate (truncating them is the only thing necessary). The I saw your rebuild command which confirmed the discrepancy.
    – Mat
    Commented Dec 10, 2012 at 11:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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