Oracle: drop interval partitions

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

1 2 3 4 5 6 7 8 9

-- 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:

1 2 3 4 5

-- 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;