关于local index drop partition

Maclean Liu(刘相兵

关于local index drop partition

SQL> select partition_name from dba_ind_partitions where index_name='LOCAL_ONE';

PARTITION_NAME
---------------------------------------------------------------------------------------------

EMPLOYEES_PART1
EMPLOYEES_PART2
EMPLOYEES_PART3

SQL> alter index LOCAL_ONE drop partition EMPLOYEES_PART1;
alter index LOCAL_ONE drop partition EMPLOYEES_PART1
            *
第 1 行出现错误:
ORA-14076: 提交的变更索引分区/子分区操作对本地分区的索引无效


You cannot explicitly drop a partition from a local index.  Instead, local
index partitions are dropped only when you drop a partition from the underlying
table. You have to disable the local partition index by making it unusable.

Example:

-- alter table make all local partition indexes unusable
ALTER TABLE &v_table_name
MODIFY PARTITION &v_part_name
UNUSABLE LOCAL INDEXES;

This will set the UNUSABLE status in the DBA_IND_PARTITIONS on all
the local partitioned indexes equipartitioned with &v_part_name.

ALTER SESSION SET skip_unusable_indexes = true;

-- load data in the corresponding partitions
INSERT ... VALUES ...

-- alter table make partition local index usable
ALTER TABLE &v_table_name
MODIFY PARTITION &v_part_name
REBUILD UNUSABLE LOCAL INDEXES;

The status of the &v_part_name partitions indexes will be now USABLE again in
DBA_IND_PARTITIONS.