Disable an Index Partition

Disable an Index Partition example

In oracle database to disable an index partition you must use the command alter index with UNUSABLE keyword.

  

-- create Range-Partitioned Global Index 
CREATE INDEX amount_idx ON orders (amount)
   GLOBAL PARTITION BY RANGE (amount)
      (PARTITION part_1 VALUES LESS THAN (1000),
       PARTITION part_2 VALUES LESS THAN (2000),
       PARTITION part_3 VALUES LESS THAN (MAXVALUE));
	   
-- disable Index Partition
ALTER INDEX amount_idx 
  MODIFY PARTITION part_2 UNUSABLE;
	 

Output:

index AMOUNT_IDX created.
index AMOUNT_IDX altered.

Check partitions

select INDEX_NAME, PARTITION_NAME, STATUS 
from USER_IND_PARTITIONS 
WHERE INDEX_NAME='AMOUNT_IDX';    

Output: