I have attached a small example of maintaining partitions.
Please try it, and you will see what I have a beef with when you do the last step...i.e. add a partition.
When a new table partition is added, the index partition has the same name as the table partiton, and the index partition resides in the table partition tablespace.
My Question is, how can I specify the index partition name when I add a new table partition. Also, how do I set the index partiton name to the correct one.
All I can think of now to do is, once the table partition is added, to either rebuild the index, with the correct partition name, and tablespace. Or drop the index partitions, and re-add it !! There has to be a better way ?
Adding Index Partitions
You cannot explicitly add a partition to a local index. Instead, a new partition is added to a local index only when you add a partition to the underlying table.
Specifically, when there is a local index defined on a table and you issue the ALTER TABLE statement to add a partition, a matching partition is also added to the local index. Oracle assigns names and default physical storage attributes to the new index partitions, but you can rename or alter them after the ADD PARTITION operation is complete.
You can effectively specify a new tablespace for an index partition in an ADD PARTITION operation by first modifying the default attributes for the index. For example, assume that a local index, q1_sales_by_region_locix, was created for list partitioned table q1_sales_by_region. If before adding the new partition q1_nonmainland, you had issued the following statement, then the corresponding index partition would be created in tablespace tbs_4.
ALTER INDEX q1_sales_by_region_locix
MODIFY DEFAULT ATTRIBUTES TABLESPACE tbs_4;
Otherwise, it would be necessary for you to use the following statement to move the index partition to tbs_4 after adding it:
ALTER INDEX q1_sales_by_region_locix REBUILD PARTITION q1_nonmainland TABLESPACE tbs_4;
I thought there would be a way to add table partitions, and have the index partition get created (implicitly) where I want it, and with the correct partition name, but, I guess this has to be done in multiple steps .