oracle 定时器时间分区_oracle分区表定时添加删除分区-CSDN博客

本文链接：https://blog.csdn.net/weixin_39795116/article/details/111967252

本文介绍了如何在Oracle数据库中管理时间分区表，包括创建添加分区`pro_ADD_PARTITION_wareid`的过程、删除分区`pro_DROP_PARTITION_wareid`的过程以及数据备份`pro_expdatainto_his`的过程。通过这些自定义过程，可以实现自动添加和删除分区，以及在删除前将数据备份到历史表中，确保数据完整性。

摘要由CSDN通过智能技术生成

创建相关procedure

pro_ADD_PARTITION_wareid ---用户添加分区的过程

pro_DROP_PARTITION_wareid ---用户删除分区的过程

pro_expdatainto_his

---用户备份要删除分区数据的过程

----环境准备

create table t_auto_p

( seq

number not null,

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;