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