创建相关procedure
pro_ADD_PARTITION_wareid ---用户添加分区的过程
pro_DROP_PARTITION_wareid ---用户删除分区的过程
pro_expdatainto_his
---用户备份要删除分区数据的过程
----环境准备
create table t_auto_p
( seq
number not null,
id
int,
constraint t_auto_p_seq primary key(seq)
)
partition by range(seq)
(
partition t_part_101 values less than(101) tablespace users
);
begin
for
i in 1 .. 100 loop
insert into t_auto_p values (i, i + 100);
end
loop;
commit;
end;
/
SQL> select count(*) from t_auto_p;
COUNT(*)
----------
100
----插入新数据报错
SQL> insert into t_auto_p values(101,101);
insert into t_auto_p values(101,101)
*
ERROR at line 1:
ORA-14400: inserted partition key does not
map to any partition
1.创建自动添加新分区的过程
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;
----手动执行该过程,自动创建新分区
----第一个参数是表名,第二个参数是添加多少个新分区,这里是5个,第三个参数是表示指定分区表的所在表空间,第四个参数是分区的范围
begin
pro_ADD_PARTITION_wareid('t_auto_p', 5, 'users',100);
end;
/
d_order ALTER TABLE t_auto_p ADD PARTITION
T_PART_201 values less than(201) TABLESPACE users
d_order ALTER TABLE t_auto_p ADD PARTITION
T_PART_301 values less than(301) TABLESPACE users
d_order ALTER TABLE t_auto_p ADD PARTITION
T_PART_401 values less than(401) TABLESPACE users
d_order ALTER TABLE t_auto_p ADD PARTITION
T_PART_501 values less than(501) TABLESPACE users
d_order ALTER TABLE t_auto_p ADD PARTITION
T_PART_601 values less than(601) TABLESPACE users
PL/SQL procedure successfully completed.
set linesize 999
set pagesize 999
col TABLE_NAME for a20
col PARTITION_NAME for a20
col HIGH_VALUE for a10
col TABLESPACE_NAME for a10
select
TABLE_NAME,PARTITION_NAME,HIGH_VALUE,HIGH_VALUE_LENGTH,TABLESPACE_NAME
from dba_tab_partitions where TABLE_NAME='T_AUTO_P';
TABLE_NAME PARTITION_NAME HIGH_VALUE HIGH_VALUE_LENGTH TABLESPACE
-------------------- --------------------
---------- ----------------- ----------
T_AUTO_P T_PART_101 101 3 USERS
T_AUTO_P T_PART_201 201 3 USERS
T_AUTO_P T_PART_301 301 3 USERS
T_AUTO_P T_PART_401 401 3 USERS
T_AUTO_P T_PART_501 501 3 USERS
T_AUTO_P T_PART_601 601 3 USERS
6 rows selected.
SQL> insert into t_auto_p values(101,101);
SQL> commit;
SQL> select * from t_auto_p
partition(t_part_201);
SEQ ID
---------- ----------
101 101
SQL> select count(*) from t_auto_p
partition(t_part_101);
COUNT(*)
----------
100
SQL> select count(*) from t_auto_p
partition(t_part_301);
COUNT(*)
----------
0
2.创建删除分区的过程
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;
3.创建备份数据的过程
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调用
此过程,本过程适合如下表:
T_AUTO_P
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) = 'T_AUTO_P' 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.seq=th.seq';
--在历史表中有多少和本此导入相同的记录
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;
---调用备份数据测试
create table t_auto_p_his as select * from
t_auto_p where 1=2;
declare
i_out varchar2(2);
begin
--
Call the procedure
pro_expdatainto_his('t_auto_p', 't_part_101', i_out);
dbms_output.put_line(i_out);
end;
/
SQL> select count(*) from t_auto_p
partition(t_part_101);
COUNT(*)
----------
100
SQL> select count(*) from t_auto_p_his;
COUNT(*)
----------
100
---调用删除分区测试(会自动做数据备份)
----第一个参数表示要删除的分区表,第二个参数表示要保留的数据量
begin
pro_DROP_PARTITION_wareid('t_auto_p',199);
end;
/
?auto_pLTER TABLE t_auto_p DROP PARTITION T_PART_101
?auto_pLTER TABLE t_auto_p DROP PARTITION
T_PART_201
?auto_pLTER TABLE t_auto_p DROP PARTITION
T_PART_301
?auto_pLTER TABLE t_auto_p DROP PARTITION
T_PART_401
PL/SQL procedure successfully completed.
SQL> select count(*) from t_auto_p_his;
COUNT(*)
----------
101
select
TABLE_NAME,PARTITION_NAME,HIGH_VALUE,HIGH_VALUE_LENGTH,TABLESPACE_NAME
from dba_tab_partitions where TABLE_NAME='T_AUTO_P';
select
index_name,index_type,tablespace_name,table_type,status from user_indexes where
table_name='T_AUTO_P';
----删除测试表,重新测试:
drop table t_auto_p;
truncate table t_auto_p_his;
--4.创建shell脚本
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]$
--5.添加cron定时任务
在oracle用户的crontab下增加一条定时任务即可:
#delete partition
1 */3 * * * sh
/home/oracle/sh/ticket_del_part.sh >> /home/oracle/sh/ticket_del_part.log