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;