oracle自增自删分区的脚本,Oracle表增删分区的脚本

一、前言

项目中的Oracle库中有一张表(假设表名为Part_Tab)，其中一列为Part_ID(int型)，计划以该列作为分区依据。

通常Part_ID的值有较固定区间(假设为1~10)，但特殊情况下可能超出此范围；此外，其值并不连续。

根据业务，要求以存储过程实现按照Part_ID增、删分区的功能。

二、设计

1 根据需求，不宜采用list型分区，万一删除某个Part_ID后忘记了，又按这个Part_ID插入数据，就会产生运行错误。最终采用传统的range型方式。

2 删除分区没有特别的难度，但在增加分区时，要考虑到如果Part_ID的值如果不比现有分区的range值都大，则不能简单地add分区，而要把某个现有分区进行split。

3 确定split哪个具体分区时，依据是user_tab_partition视图的high_value字段，但很不幸该字段为long类型，无法直接用在where条件中(报Ora-00997错误)，虽然可转换成number型，但过程却十分麻烦(不能直接使用to_number，报Ora-00932错误)。

4 为此进行一点变通，将该表的分区名设定为：“表名_“||指定位数的PART_ID(这应该也是许许多多表分区名的传统方法)，再以user_tab_partition视图的parttion_name进行判断就方便多了。

三、建表脚本

--创建表按较固定的范围创建初始分区

create tablePART_TAB

(

PART_IDint not null,

...primary key(PART_ID, ...)

)

PARTITIONBYRANGE(PART_ID)

(

PARTITION PART_TAB_01VALUES LESS THAN (2) TABLESPACE ...,

PARTITION PART_TAB_02VALUES LESS THAN (3) TABLESPACE ...,

PARTITION PART_TAB_03VALUES LESS THAN (4) TABLESPACE ...,

PARTITION PART_TAB_04VALUES LESS THAN (5) TABLESPACE ...,

PARTITION PART_TAB_05VALUES LESS THAN (6) TABLESPACE ...,

PARTITION PART_TAB_06VALUES LESS THAN (7) TABLESPACE ...,

PARTITION PART_TAB_07VALUES LESS THAN (8) TABLESPACE ...,

PARTITION PART_TAB_08VALUES LESS THAN (9) TABLESPACE ...,

PARTITION PART_TAB_09VALUES LESS THAN (10) TABLESPACE ...,

PARTITION PART_TAB_10VALUES LESS THAN (11) TABLESPACE ...,

);

四、删除分区脚本

--存储过程：删除指定分区及数据--如果该分区不存在则直接退出

create or replace procedure DROP_PART(pPartID in number) IScntint;

vPartNamevarchar2(20);BEGINvPartName := 'PART_TAB_' || trim(to_char(pPartID, '09'));select count(*) intocntfromUSER_TAB_PARTITIONSwhere table_name = 'PART_TAB'

and partition_name =vPartName;if cnt >= 1 then

execute immediate 'alter table PART_TAB DROP PARTITION' ||vPartName;end if;END;

五、增加分区脚本

--存储过程：增加指定分区--如果该分区已存在则直接退出

create or replace procedure ADD_PART(pPartID in number) IScntint;

vNewPartNamevarchar2(20);

vOldPartNamevarchar2(20);BEGINvNewPartName := 'PART_TAB_' || trim(to_char(pPartID, '09'));select count(*) intocntfromUSER_TAB_PARTITIONSwhere table_name = 'PART_TAB'

and partition_name =vNewPartName;if cnt < 1 then

--如果指定分区不存在，则继续

select min(partition_name) intovOldPartNamefromUSER_TAB_PARTITIONSwhere table_name = 'PART_TAB'

and partition_name >vNewPartName;if vOldPartName is null then

--仅当新分区的Range值比全部已有分区的Range值时，直接增加分区

execute immediate 'alter table PART_TAB ADD PARTITION' ||vNewPartName|| 'values less than (' ||to_char(pPartID+ 1) || ')';else

--否则，要把最接近的分区按Range值进行split

execute immediate 'alter table PART_TAB SPLIT PARTITION' ||vOldPartName|| 'AT (' || to_char(pPartID + 1) ||

') into (PARTITION' || vNewPartName ||

', PARTITION' || vOldPartName || ')';end if;end if;END;

PS：脚本没有考虑到表分区在不同表空间的情况(业务需求如此)，加上也不难，对表空间名字做个类似限制即可，这里不再展开。