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:
SQL> ALTER TABLE owner_name.SAMPLE_PARTITIONED_TBL 2 TRUNCATE PARTITION SMPL_201001 DROP STORAGE;
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 2 where INDEX_NAME like 'SAMPLE_PARTITIONED_TBL_PK'; STATUS -------- UNUSABLE
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';
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
SAMPLE_PARTITIONED_TBL_PK is missing in the result set thus confirming Mat's observation. :)