Drop an Index Partition

Drop an Index Partition example

In oracle database to drop an index partition you must use the command alter index with DROP PARTITION 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));
	   
-- drop Index Partition
ALTER INDEX AMOUNT_IDX
  DROP PARTITION part_2;
	 

Output:

index AMOUNT_IDX created.
index AMOUNT_IDX altered.

Check partitions

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

Output: