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

最新推荐文章于 2022-07-09 15:16:57 发布

cuimiwan3140

最新推荐文章于 2022-07-09 15:16:57 发布

阅读量559

点赞数 1

文章标签：数据库 shell

创建相关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(*)

----------

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 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：之前已经导入过了）

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

reference http://blog.csdn.net/wyzxg/article/details/4076996/

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30130773/viewspace-2123539/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/30130773/viewspace-2123539/

cuimiwan3140

关注

1
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
oracle分区表定时添加删除分区

创建相关procedure pro_ADD_PARTITION_wareid ---用户添加分区的过程 pro_DROP_PARTITION_wareid ---用户删...
复制链接

扫一扫