Partitioned Indexes - Expert Indexing in Oracle Database 11g

Databases Reference

In-Depth Information

You can also drop partitions of a globally partitioned index. However, when doing so, if the partition

being dropped contains index entries, the higher adjacent partition will be marked unusable. In the

following code, you are querying the status of the index partitions prior to dropping partition

MANAGER_125:

SQL> SELECT partition_name, status from user_ind_partitions

2* WHERE index_name = 'EMPLOYEES_GI2';

PARTITION_NAME STATUS

------------------------------ --------

MANAGER_100 USABLE

MANAGER_125 USABLE

MANAGER_200 USABLE

MANAGER_300 USABLE

MANAGER_400 USABLE

MANAGER_500 USABLE

MANAGER_600 USABLE

MANAGER_700 USABLE

MANAGER_800 USABLE

MANAGER_900 USABLE

MANAGER_MAX USABLE

Then you drop partition MANAGER_125.

SQL> ALTER index employees_i2

2 DROP partition manager_125;

Index altered.

When you query USER_IND_PARTITONS again, you can see it marked the higher partition UNUSABLE .

SQL> select partition_name, status from user_ind_partitions

2* where index_name = 'EMPLOYEES_GI2';