Oracle: drop interval partitions

source: http://prutser.wordpress.com/2010/01/11/dropping-interval-partitions/

-- Count number of partitions: 905
select count(*) from dba_tab_partitions where table_name = 'AT_PROCESS_LOG';

-- List of partitions to drop: keep 30 latest partitions
select * from (
select partition_name, interval, high_value, partition_position
from dba_tab_partitions where table_name = 'AT_PROCESS_LOG'
order by partition_position desc
) where 1 < partition_position and partition_position < 905 - (30-1);

Drop syntax examples:

-- Drop partition w/ high_value = 16-03-2012
alter table monitor_db.at_process_log drop partition for (to_date('15-03-2012','DD-MM-YYYY')); 

-- Drop partition from name
alter table monitor_db.at_process_log drop partition SYS_P61;