oracle db 间隔分区妙用

     线上数据库有几张log 表，每天新增插入行数2千多万，开发之前是使用delete 每天删除一定数据量，后面随着日志表越来越大，删除的sql 执行速度越来越慢，db redo、undo 量暴增，删除速度慢(执行计划都是全表扫描),影响db系统性能。与开发沟通，得知log 只需要保留最近7天的，7天前的都可以删除，与开发协商，将几张log 表改造为基于插入时间栏位的间隔分区表，按照天每天自动产生一个分区，每天新的数据插入新的分区，dba部署db job 每天定期删除7天之前的表分区，drop partition 是ddl ，执行速度快，数据瞬间删除，对性能影响很小，目前已经平稳运行大半年，记录之。

       drop 分区存储过程代码如下,大家可以根据自己的实际环境进行修改调整：

CREATE OR REPLACE PROCEDURE SP_DROP_PATITION_LOG As
v_SqlExec_1 VARCHAR2(2000);
v_err_num NUMBER;
v_err_msg VARCHAR2(100);
cursor cursor_part_info_1 is
select t.SUBOBJECT_NAME partition_name
from dba_objects t
where object_name = upper('table_name')
and t.OBJECT_TYPE = 'TABLE PARTITION'
and t.GENERATED = 'Y' -- the first partition is not allowed to drop
and t.CREATED < sysdate - 7; -- look for partition before 7 days
record_part_info_1 cursor_part_info_1%rowType;

BEGIN
open cursor_part_info_1;
loop
fetch cursor_part_info_1
into record_part_info_1;
exit when cursor_part_info_1%notfound;
--delete the partition of table_name
v_SqlExec_1 := 'ALTER TABLE table_name DROP PARTITION ' ||record_part_info_1.partition_name;
dbms_output.put_line('delete table_name partition=' ||record_part_info_1.partition_name );
dbms_utility.exec_ddl_statement(v_SqlExec_1);
end loop;
close cursor_part_info_1;

EXCEPTION
WHEN OTHERS THEN
v_err_num := SQLCODE;
v_err_msg := SUBSTR(SQLERRM, 1, 100);
dbms_output.put_line('DROP_PATITION_LOG fail,error code=' || v_err_num || 'err_desc=' || v_err_msg);
END;