Partition High Value

I have a table that is partitioned by month. I only want 3 months worth of data in it. I am trying to write a program that identifies the paritions that hold values older then 3 months and drop them. This script would run once a month.

When I try to use the high value column in dba_tab_partitions it is long and I can't sort on it to get the last three. Any ideas how I could get the partition name of the partitions that I want dropped?

If you have used sensible partition names, then it would be easy.

for example if your partition for July 2003 is called "P2003_07" then you can perform ...

Code:

---

begin
  execute immediate
      'alter table
        my_table
      drop partition '||
      to_char(add_months(sysdate,-4),'"P"YYYY"_"MM');
end;
/

---

Instead of the high_value, use the partition_position from dba_Tab_partitions.