Oracle 11g自动创建分区（INTERVAL PATITION）后的定时删分区

文章由LinuxBoy分享于2019-03-30 03:03:22热评（620）

Oracle 11g自动创建分区（INTERVAL PATITION）后的定时删分区

TRUCK_GPS_TRANS表利用Oracle11的INTERVAL PATITION自动创建分区功能按天分区，现在要定时删除30天之前的分区数据，保持表里只有30天的业务数据，因为分区是动态创建，无法获得分区名，所以要用到字典表user_objects ，存储过程为：

CREATE OR REPLACE PROCEDURE PROC_DROP_PATITION_GPS_TRANS(beforeDays NUMBER
) As
v_SqlExec VARCHAR2(2000);
v_err_num NUMBER;
v_err_msg VARCHAR2(100);

cursor cursor_cpu_info_part is
select t.SUBOBJECT_NAME partition_name
from user_objects t
where object_name = upper('TRUCK_GPS_TRANS')
and t.OBJECT_TYPE = 'TABLE PARTITION'
and t.GENERATED ='Y' --第一个分区也就是创建表时候创建的第一个分区是不允许被删除的，oracle报错，自动GENERATED 的分区是可以删除的
and t.CREATED < sysdate - 30; -- 寻找30天之前的
record_cpu_info_oldpart cursor_cpu_info_part%rowType;

BEGIN
open cursor_cpu_info_part;
loop
fetch cursor_cpu_info_part
into record_cpu_info_oldpart;
exit when cursor_cpu_info_part%notfound;
--删除 TRUCK_GPS_TRANS表分区
v_SqlExec := 'ALTER TABLE TRUCK_GPS_TRANS DROP PARTITION ' ||
record_cpu_info_oldpart.partition_name;
dbms_output.put_line('删除TRUCK_GPS_TRANS表分区=' || v_SqlExec);
DBMS_Utility.Exec_DDL_Statement(v_SqlExec);
end loop;
close cursor_cpu_info_part;

EXCEPTION
WHEN OTHERS THEN
v_err_num := SQLCODE;
v_err_msg := SUBSTR(SQLERRM, 1, 100);
dbms_output.put_line('MALS_NM_DROP_PARTITION_PROC执行出现异常,错误码=' ||
v_err_num || '错误描述=' || v_err_msg);
END PROC_DROP_PATITION_GPS_TRANS;

Oracle 11g自动创建分区（INTERVAL PATITION）后的定时删分区