oracle定时添加或删除分区表的分区

这几天，根据业务的需求和性能的考虑，需要定时删除历史数据，表里
只保留最近指定的wareid的数据，考虑删除时对表的影响及便利性，我决定
把这个表建立成分区表，定期添加和删除分区

步骤：


1. 创建相关procedure
pro_ADD_PARTITION_wareid ---用户添加分区的过程
pro_DROP_PARTITION_wareid ---用户删除分区的过程
pro_expdatainto_his ---用户备份要删除分区数据的过程


2.创建系统上的脚本


3.用cron做定时任务


1. 创建相关procedure


CREATE OR REPLACE PROCEDURE pro_ADD_PARTITION_wareid(tablename varchar2, ---要添加分区的表
partNum NUMBER, --添加分区的个数
TableSpaceName VARCHAR2, --表空间名
wareidnum number default 100 ---分区的范围
) AS
/*******************************************************
author：skate
time ：2009/02/28
功能：添加分区表的指定分区
说明：可以通过定时任务来完成自动添加分区，添加后要检查索引的状态
最好是重建索引，以达到对表的分析
eg： exec pro_add_partition_wareid('d_order_bak',2,'yytickets','200');
********************************************************/

v_SqlExec VARCHAR2(2000); --DDL语句变量
v_Partwareid1 number; --创建分区的wareid
v_err_num NUMBER; --ORA错误号
v_err_msg VARCHAR2(100); --错误描述

v_part_wareid_max number; --tablename 表分区的最大wareid号
v_begin number; ----字符串的开始位置
v_count number; ----取多少个字符串
v_part_name varchar2(100); --要添加分区表的名称的前缀

BEGIN

----字符串的开始位置
select INSTR(partition_name, '_', -1, 1) + 1
into v_begin
from user_tab_partitions
where table_name = UPPER(tablename)
and rownum < 2;

----取多少个字符串
select length(partition_name) - v_begin + 1
into v_count
from user_tab_partitions
where table_name = UPPER(tablename)
and rownum < 2;

--查询分区表tablename的最大wareid值
select max(to_number(SUBSTR(partition_name, v_begin, v_count)))
into v_part_wareid_max
from user_tab_partitions
WHERE table_name = UPPER(tablename);

---计算分区表的名称
select SUBSTR(partition_name, 1, v_begin - 1)
into v_part_name
from user_tab_partitions
WHERE table_name = UPPER(tablename)
and rownum < 2;

v_Partwareid1 := v_part_wareid_max;

FOR i IN 1 .. partNum LOOP

v_Partwareid1 := v_Partwareid1 + wareidnum; ----计算要添加分区的wareid



v_SqlExec := 'ALTER TABLE ' || tablename || ' ADD PARTITION ' ||
v_part_name || v_Partwareid1 || ' values less than(' ||
v_Partwareid1 || ') TABLESPACE ' || TableSpaceName;
dbms_output.put_line('创建 d_order 表分区' || i || '=' || v_SqlExec);
DBMS_Utility.Exec_DDL_Statement(v_SqlExec);

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

##########################################################################################

删除分区的procedure：

CREATE OR REPLACE PROCEDURE pro_DROP_PARTITION_wareid(tablename varchar2, ---要删除分区表的名称
beforewareid NUMBER default 200 --要保留最新多少wareid
) As

/*******************************************************
author：skate
time ：2009/02/28
功能：删除分区表的指定分区
说明：可以通过定时任务来完成自动删除分区，删除后要检查索引的状态
最好是重建索引，以达到对表的分析

eg：exec pro_drop_partition_wareid('d_order_bak',10800);
********************************************************/

v_SqlExec VARCHAR2(2000); --DDL语句变量
v_err_num NUMBER; --ORA错误号
v_err_msg VARCHAR2(100); --错误描述

v_begin number; ----字符串的开始位置
v_count number; ----取多少个字符串
v_ware_id number;
v_max_ware_id number;
b varchar2(10);

cursor cursor_table_part is
select partition_name
from user_tab_partitions
WHERE table_name = UPPER(tablename)
AND SUBSTR(partition_name, v_begin, v_count) < v_ware_id
ORDER BY partition_name;
---为禁用或启用约束而定义的游标
cursor cursor_const_part is
select b.constraint_name
from user_constraints a, user_cons_columns b
where a.table_name=b.table_name
and a.constraint_name=b.constraint_name
and a.owner='TICKETS'
and a.table_name=upper(tablename);

---为索引失效而重建索引定义游标
cursor cursor_idx is
select index_name
from user_indexes
where table_name = UPPER(tablename)
and status = 'UNUSABLE';


record_table_oldpart cursor_table_part%rowType;
record_cursor_const_part cursor_const_part%rowtype;
record_cursor_idx cursor_idx%rowtype;

BEGIN

----字符串的开始位置
select INSTR(partition_name, '_', -1, 1) + 1
into v_begin
from user_tab_partitions
where table_name = UPPER(tablename)
and rownum < 2;

----取多少个字符串
select length(partition_name) - v_begin + 1
into v_count
from user_tab_partitions
where table_name = UPPER(tablename)
and rownum < 2;

--确定要删除的最大wareid
select max(to_number(substr(partition_name, v_begin, v_count)))
into v_max_ware_id
from user_tab_partitions
where table_name = UPPER(tablename);

--select max(w.ware_id) into v_max_ware_id from d_ware w;
v_ware_id := v_max_ware_id - beforewareid;

---暂时禁用约束
open cursor_const_part;
loop
fetch cursor_const_part into record_cursor_const_part;
exit when cursor_const_part%notfound;

execute immediate 'alter table '||tablename||' disable constraint '||record_cursor_const_part.constraint_name||' cascade';

end loop;
close cursor_const_part;

open cursor_table_part;
loop
fetch cursor_table_part
into record_table_oldpart;
exit when cursor_table_part%notfound;

if substr(record_table_oldpart.partition_name, v_begin, v_count) <
v_ware_id then
--把要删除的数据提前备份到历史表里
pro_expdatainto_his(tablename,record_table_oldpart.partition_name,b);

--验证是否可以删除指定的分区表
if (b='Y' or b='R') then


--删除 tablename 表分区

v_SqlExec := 'ALTER TABLE ' || tablename || ' DROP PARTITION ' ||
record_table_oldpart.partition_name;
dbms_output.put_line('删除' || tablename || '表分区=' || v_SqlExec);
DBMS_Utility.Exec_DDL_Statement(v_SqlExec);



elsif b='N' then
exit;
end if;

end if;
end loop;
close cursor_table_part;

---启用约束
open cursor_const_part;
loop
fetch cursor_const_part into record_cursor_const_part;
exit when cursor_const_part%notfound;

execute immediate 'alter table '||tablename||' enable novalidate constraint '||record_cursor_const_part.constraint_name;

end loop;
close cursor_const_part;

--重建失效的索引
open cursor_idx;
loop
fetch cursor_idx into record_cursor_idx;
exit when cursor_idx%notfound;

execute immediate 'alter index ' ||record_cursor_idx.index_name||' rebuild';

end loop;
close cursor_idx;

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


END pro_DROP_PARTITION_wareid;


#########################################################################################

这个存储过程用户在删除分区的时候，用户备份所删除的分区表


create or replace procedure pro_expdatainto_his(tablename varchar2,---要导数据的表名
part_tablename varchar2,---要导数据的分区表名
issuccess in out varchar2)--返回是否导入成功 （Y：成功 N：失败 R：之前已经导入过了）
as

v_SqlExechis VARCHAR2(2000); --DDL语句变量
v_SqlExec VARCHAR2(2000); --DDL语句变量
v_count number(30); --本次导入的数据行数
v_expcount number(30); --历史表中有多少符合本次导入的行数
/*******************************************************
author：skate
time ：2009/04/15
功能：把预删除的分区表的数据提前导入到历史表中
说明：这个程序是配合定时删除分区的过程，pro_add_partition_wareid调用
此过程，本过程适合如下表：
D_ORDER
D_ORDER_DETAIL
D_PRINT_BATCH
d_batch_detail

如果想让其适合其他表，只要做简单修改即可

eg： exec pro_expdatainto_his('d_order','D_ORDER_P_31100',b);

注意：本procedure的表名用到了变量，所以要用动态sql来执行：execute immediate
********************************************************/

begin


if upper(tablename) = 'D_ORDER' then

v_SqlExec := 'select count(1) from ' || tablename || ' partition(' ||
part_tablename || ')';
--准备导入多少行数据
execute immediate v_SqlExec
into v_count;
--执行导入操作
execute immediate 'insert into ' || tablename ||
'_his select * from '||tablename||' partition(' ||
part_tablename || ') nologing';

v_SqlExechis := 'select count(1) from ' || tablename || '_his th ,' ||
tablename || ' partition(' || part_tablename ||
') t where t.order_id=th.order_id';

--在历史表中有多少和本此导入相同的记录
execute immediate v_SqlExechis
into v_expcount;

elsif upper(tablename) = 'D_ORDER_DETAIL' then

v_SqlExec := 'select count(1) from ' || tablename || ' partition(' ||
part_tablename || ')';
execute immediate v_SqlExec
into v_count;

execute immediate 'insert into ' || tablename ||'_his select * from '||tablename||' partition('||part_tablename || ') nologing';

v_SqlExechis := 'select count(1) from ' || tablename || '_his th ,' ||tablename || ' partition(' || part_tablename ||') t where t.detail_id=th.detail_id';

execute immediate v_SqlExechis
into v_expcount;

elsif upper(tablename) = 'D_PRINT_BATCH' then

v_SqlExec := 'select count(1) from ' || tablename || ' partition(' ||
part_tablename || ')';
execute immediate v_SqlExec
into v_count;

execute immediate 'insert into ' || tablename ||
'_his select * from '||tablename||' partition(' ||
part_tablename || ') nologing';

v_SqlExechis := 'select count(1) from ' || tablename || '_his th ,' ||
tablename || ' partition(' || part_tablename ||
') t where t.batch_id=th.batch_id';

execute immediate v_SqlExechis
into v_expcount;

elsif upper(tablename) = upper('d_batch_detail') then

v_SqlExec := 'select count(1) from ' || tablename || ' partition(' ||
part_tablename || ')';
execute immediate v_SqlExec
into v_count;

execute immediate 'insert into ' || tablename ||
'_his select * from '||tablename||' partition(' ||
part_tablename || ') nologing';

v_SqlExechis := 'select count(1) from ' || tablename || '_his th ,' ||
tablename || ' partition(' || part_tablename ||
') t where t.batch_detail_id=th.batch_detail_id';

execute immediate v_SqlExechis
into v_expcount;

end if;

--验证是否导入成功，如果成功就commit，否则rollback
if v_count = v_expcount then
issuccess := 'Y';
commit;
elsif v_count * 2 = v_expcount then
issuccess := 'R';
rollback;
else
issuccess := 'N';
rollback;
end if;

end pro_expdatainto_his;

################################################################

2.创建系统上的脚本

[oracle@svr-db-test sh]$ more ticket_del_part.sh
cd /home/oracle/sh
date
sqlplus /nolog @ ticket_del_part.sql
date
[oracle@svr-db-test sh]$


[oracle@svr-db-test sh]$ more ticket_del_part.sql
connect tickets/123456
set timing on
exec pro_drop_partition_wareid('d_order',50);
exec pro_drop_partition_wareid('d_order_detail',50);
exec pro_drop_partition_wareid('D_PRINT_BATCH',50);

exit
[oracle@svr-db-test sh]$

3.用cron做定时任务

在oracle用户的crontab下增加一条定时任务即可：


#delete partition

1 */3 * * * sh /home/oracle/sh/ticket_del_part.sh >> /home/oracle/sh/ticket_del_part.log


简单吧！！！


真TMD累啊！！ 回家


------end-----