oracle自动建立分区interval,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;

推荐阅读：