Quote Originally Posted by slimdave
you can query ALL_PART_INDEXES.LOCALITY for that info.
Sure. But with those three views I was not aiming for the OP to find out if the index is indeed local or not. I ment if he is allready certain that index indeed is local, with those three views he would be able to find out himself that the HIGH_VALUE in both corresponding table and index partitions are the same (to show that there is no bug in oracle - when you drop the partition, the corresponding local partition of the index is dropped too). However I didn't realized until now that he is not using RANGE partitioning, but rather LIST. So indeed those three views are rather useless in his situation. My bad.

Quote Originally Posted by summer06
The weird thing is that the partition_name in index is different from partition_name in the table. What happened?
The name of the partition does not tell anything about the deffinition or the organization of the partition. So you can't tell absolutely anything about the partition from its name, realy. What realy happened in your example, apparantly, is that someone named partitions with no actual correspondence between their names and their partition key values.

BTW, from the names of your table and index partitions I would deduce that you are partitioning by some date column. Wouldn't RANGE partitioning scheme be more appropriate than a LIST one?