function
CREATE OR REPLACE FUNCTION GET_HIGH_VALUE_AS_DATE (
p_TableName IN VARCHAR2,
p_PatitionName IN VARCHAR2
) RETURN DATE
IS
v_LongVal LONG;
BEGIN
SELECT HIGH_VALUE INTO v_LongVal
FROM USER_TAB_PARTITIONS
WHERE TABLE_NAME = p_TableName
AND PARTITION_NAME = p_PatitionName;
RETURN TO_DATE(substr(v_LongVal, 11, 19), 'YYYY-MM-DD HH24:MI:SS');
END GET_HIGH_VALUE_AS_DATE;
/
calls function
Set serverout on size 1000000
declare
cursor tab_part_cur IS
select table_name,partition_name from user_tab_partitions where table_name='BOMR_MESSAGE_LOG' order by 1,2;
v_date date;
v_pass integer := 0;
begin
dbms_output.put_line('These partitions can be dropped.');
for tab_part_rec in tab_part_cur
loop
exit when tab_part_cur%notfound;
v_date := GET_HIGH_VALUE_AS_DATE(tab_part_rec.table_name,tab_part_rec.partition_name);
if v_date <= (sysdate - 10) then
dbms_output.put_line(tab_part_rec.table_name||' '||tab_part_rec.partition_name|| ' '||v_date );
end if;
end loop;
end;
/
