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;
推荐阅读：