Oracle: drop partitions

Dropping a partition with a non local index

create table tq84_range_partition (
  id   number,
  txt  varchar2(15),
  dt   date
)
partition by range (dt) (
  partition tq84_range_partition_2009  values less than (date '2010-01-01'),
  partition tq84_range_partition_2010  values less than (date '2011-01-01'),
  partition tq84_range_partition_2011  values less than (date '2012-01-01'),
  partition tq84_range_partition_max   values less than (      maxvalue   )
);


create unique index tq84_non_local on tq84_range_partition (id);

insert into tq84_range_partition values( 9, 'nine'  , date '2009-09-09');
insert into tq84_range_partition values(10, 'ten'   , date '2010-10-10');
insert into tq84_range_partition values(11, 'eleven', date '2011-11-11');
insert into tq84_range_partition values(12, 'twelve', date '2012-12-12');



select status from user_indexes where index_name = 'TQ84_NON_LOCAL';
--
-- VALID
--

--
-- Droping one partition:
--
alter table tq84_range_partition drop partition tq84_range_partition_2010;

select status from user_indexes where index_name = 'TQ84_NON_LOCAL';
--
-- UNUSABLE
--

--
-- Because the index is unusable AND unique, the following statement generates
--
--      ORA-01502: index 'SNB_DBA.TQ84_NON_LOCAL' or partition of
--      such index is in unusable state
--
-- If the index were not unique, the insert statement would not fail.
--
insert into tq84_range_partition values(13, 'does not work', date '2011-05-17');
-- 
--         ID TXT             DT
-- ---------- --------------- -------------------
--          9 nine            09.09.2009 00:00:00
--         11 eleven          11.11.2011 00:00:00
--         12 twelve          12.12.2012 00:00:00
-- 
select * from tq84_range_partition;

drop table tq84_range_partition purge;

See also