Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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:

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.)

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.
–
MatDec 10 '12 at 11:10