下面为自动删除、创建分区子表的存储过程和维护分区表的测试示例：
1
、每月定时增加一个分区子表
create or replace procedure addpartition(sTable_name varchar(100)) is
declare
highvalue varchar(50);
partition_name varchar(50);
month_name varchar(2);
year_name varchar(4);
sSql varchar(1000);
preSql varchar(1000);
posSql varchar(1000);
begin
select substr(max(high_value),10,10) into highvalue from all_tab_partitions where table_name=sTable_name and table_owner=‘
TEST
’ and HIGH_VALUE like ‘DATETIME%’;
–print highvalue;
year_name = datepart(year,highvalue);
if datepart(month,highvalue)<10 then
month_name = ‘0’ || datepart(month,highvalue);
else
month_name = datepart(month,highvalue);
end if;
partition_name = ‘P_’ || year_name || month_name;
highvalue=dateadd(MM,1,highvalue);
year_name = datepart(year,highvalue);
month_name=datepart(month,highvalue);
sSql := ‘alter table ’ || sTable_name || ’ add partition ’ || partition_name || ’ values less than(DATETIME’’’ || year_name || ‘-’ || month_name || ‘-01 00:00:00’’) storage (on MAIN,CLUSTERBTR)’;
preSql := 'alter table ‘|| sTable_name || ’ drop partition P_299999’;
posSql := 'alter table ‘|| sTable_name || ’ add partition P_299999 values less than (MAXVALUE)storage(on MAIN,CLUSTERBTR)’;
execute immediate preSql;
–print preSql;
execute immediate sSql;
–print sSql;
execute immediate posSql;
–print posSql;
end;
2
、每月定时删除一个最
久
分区
create or replace procedure
TEST
.delpartition(sTable_name varchar(100))
is
declare
cursor del_part_name;
highvalue varchar(50);
partt_name varchar(50);
delSql varchar(500);
begin
select
substr(max(high_value), 10, 10)
into
highvalue
from
all_tab_partitions
where
table_name =sTable_name
and table_owner=‘
TEST
’
and HIGH_VALUE like ‘DATETIME%’;
highvalue = ADD_MONTHS(highvalue, -15);
--print highvalue;
open del_part_name for select partition_name from all_tab_partitions where table_name=sTable_name and table_owner='TEST' and substr(high_value, 10, 10) <= highvalue and partition_name <> 'P_299999' order by 1;
loop
fetch del_part_name into partt_name;
--alter table sTable_name drop PARTITION partt_name;
delSql := 'alter table '|| sTable_name || ' drop partition '||partt_name;
exit when del_part_name%NOTFOUND;
execute immediate delSql;
end loop;
close del_part_name;
end;
3
、备份分区
可以采用逻辑导入导出进行备份，但是前提需要保证待还原的分区子表没有被drop掉，否则会在当前模式下创建普通表进行还原。
4
、回收空间
1>可以采用truncate table or partition_name 及时释放单表或单个分区子表的磁盘空间；delete和drop操作并不能及时释放磁盘空间，需要重新启动数据库；
2>可以采用手动清空undo段释放磁盘空间，存储过程如下：
create or replace procedure remove_space()
as
begin
EXECUTE IMMEDIATE ‘alter system set ‘‘UNDO_RETENTION’’=1;’;
dbms_lock.sleep(15);–时间可以根据实际情况进行设定
EXECUTE IMMEDIATE ‘alter system set ‘‘UNDO_RETENTION’’=900;’;
end;
5
、测试示例：
–1–创建测试的分区表ET_AIRPORTCONTROL
CREATE TABLE “ET_AIRPORTCONTROL”
(
“PURGE_DAT” TIMESTAMP(0),
“LKEY” NUMBER(16,0) NOT NULL,
“TKNB” CHAR(13) NOT NULL,
“CNBR” NUMBER(3,0) NOT NULL,
“ACAL” CHAR(3),
“ASAC” CHAR(16),
“TEXT” VARCHAR2(200))
PARTITION BY RANGE(“PURGE_DAT”)
(
PARTITION “P_201505” VALUES LESS THAN(DATETIME’2015-06-01 00:00:00’) STORAGE(ON “MAIN”, CLUSTERBTR) ,
PARTITION “P_201506” VALUES LESS THAN(DATETIME’2015-07-01 00:00:00’) STORAGE(ON “MAIN”, CLUSTERBTR) ,
PARTITION “P_201507” VALUES LESS THAN(DATETIME’2015-08-01 00:00:00’) STORAGE(ON “MAIN”, CLUSTERBTR) ,
PARTITION “P_201508” VALUES LESS THAN(DATETIME’2015-09-01 00:00:00’) STORAGE(ON “MAIN”, CLUSTERBTR) ,
PARTITION “P_201509” VALUES LESS THAN(DATETIME’2015-10-01 00:00:00’) STORAGE(ON “MAIN”, CLUSTERBTR) ,
PARTITION “P_201510” VALUES LESS THAN(DATETIME’2015-11-01 00:00:00’) STORAGE(ON “MAIN”, CLUSTERBTR) ,
PARTITION “P_201511” VALUES LESS THAN(DATETIME’2015-12-01 00:00:00’) STORAGE(ON “MAIN”, CLUSTERBTR) ,
PARTITION “P_201512” VALUES LESS THAN(DATETIME’2016-01-01 00:00:00’) STORAGE(ON “MAIN”, CLUSTERBTR) ,
PARTITION “P_201601” VALUES LESS THAN(DATETIME’2016-02-01 00:00:00’) STORAGE(ON “MAIN”, CLUSTERBTR) ,
PARTITION “P_201602” VALUES LESS THAN(DATETIME’2016-03-01 00:00:00’) STORAGE(ON “MAIN”, CLUSTERBTR) ,
PARTITION “P_201603” VALUES LESS THAN(DATETIME’2016-04-01 00:00:00’) STORAGE(ON “MAIN”, CLUSTERBTR) ,
PARTITION “P_201604” VALUES LESS THAN(DATETIME’2016-05-01 00:00:00’) STORAGE(ON “MAIN”, CLUSTERBTR) ,
PARTITION “P_201605” VALUES LESS THAN(DATETIME’2016-06-01 00:00:00’) STORAGE(ON “MAIN”, CLUSTERBTR) ,
PARTITION “P_201606” VALUES LESS THAN(DATETIME’2016-07-01 00:00:00’) STORAGE(ON “MAIN”, CLUSTERBTR) ,
PARTITION “P_201607” VALUES LESS THAN(DATETIME’2016-08-01 00:00:00’) STORAGE(ON “MAIN”, CLUSTERBTR) ,
PARTITION “P_201608” VALUES LESS THAN(DATETIME’2016-09-01 00:00:00’) STORAGE(ON “MAIN”, CLUSTERBTR) ,
PARTITION “P_201609” VALUES LESS THAN(DATETIME’2016-10-01 00:00:00’) STORAGE(ON “MAIN”, CLUSTERBTR) ,
PARTITION “P_201610” VALUES LESS THAN(DATETIME’2016-11-01 00:00:00’) STORAGE(ON “MAIN”, CLUSTERBTR) ,
PARTITION “P_201611” VALUES LESS THAN(DATETIME’2016-12-01 00:00:00’) STORAGE(ON “MAIN”, CLUSTERBTR) ,
PARTITION “P_201612” VALUES LESS THAN(DATETIME’2017-01-01 00:00:00’) STORAGE(ON “MAIN”, CLUSTERBTR) ,
PARTITION “P_201701” VALUES LESS THAN(DATETIME’2017-02-01 00:00:00’) STORAGE(ON “MAIN”, CLUSTERBTR) ,
PARTITION “P_201702” VALUES LESS THAN(DATETIME’2017-03-01 00:00:00’) STORAGE(ON “MAIN”, CLUSTERBTR) ,
PARTITION “P_201703” VALUES LESS THAN(DATETIME’2017-04-01 00:00:00’) STORAGE(ON “MAIN”, CLUSTERBTR) ,
PARTITION “P_201704” VALUES LESS THAN(DATETIME’2017-05-01 00:00:00’) STORAGE(ON “MAIN”, CLUSTERBTR) ,
PARTITION “P_201705” VALUES LESS THAN(DATETIME’2017-06-01 00:00:00’) STORAGE(ON “MAIN”, CLUSTERBTR) ,
PARTITION “P_201706” VALUES LESS THAN(DATETIME’2017-07-01 00:00:00’) STORAGE(ON “MAIN”, CLUSTERBTR) ,
PARTITION “P_201707” VALUES LESS THAN(DATETIME’2017-08-01 00:00:00’) STORAGE(ON “MAIN”, CLUSTERBTR) ,
PARTITION “P_299999” VALUES LESS THAN(MAXVALUE) STORAGE(ON “MAIN”, CLUSTERBTR)
) STORAGE(ON “MAIN”, CLUSTERBTR) ;
–2–
创建存储过程
–1>定时增加分区
create or replace
procedure addpartition
(sTable_name varchar(100))
is
declare
highvalue varchar(50);
partition_name varchar(50);
month_name varchar(2);
year_name varchar(4);
sSql varchar(1000);
preSql varchar(1000);
posSql varchar(1000);
begin
select
substr(max(high_value), 10, 10)
into
highvalue
from
all_tab_partitions
where
table_name =sTable_name
and table_owner=‘SYSDBA’ —语句中的table_owner可以修改为指定模式名
and HIGH_VALUE like ‘DATETIME%’;
–print highvalue;
year_name = datepart(year, highvalue);
if datepart(month, highvalue)<10 then
month_name = ‘0’ || datepart(month, highvalue);
else
month_name = datepart(month, highvalue);
end if;
partition_name = ‘P_’ || year_name || month_name;
highvalue =dateadd(MM, 1, highvalue);
year_name = datepart(year, highvalue);
month_name =datepart(month, highvalue);
sSql := ‘alter table ’ || sTable_name || ’ add partition ’ || partition_name || ’ values less than(DATETIME’’’ || year_name || ‘-’ || month_name || ‘-01 00:00:00’’) storage (on MAIN,CLUSTERBTR)’;
preSql := 'alter table ‘|| sTable_name || ’ drop partition P_299999’;
posSql := 'alter table ‘|| sTable_name || ’ add partition P_299999 values less than (MAXVALUE)storage(on MAIN,CLUSTERBTR)’;
execute immediate preSql;
–print preSql;
execute immediate sSql;
–print sSql;
execute immediate posSql;
–print posSql;
end;
–2>
定时删除分区
create or replace procedure delpartition
(sTable_name varchar(100))
is
declare
cursor del_part_name;
highvalue varchar(50);
partt_name varchar(50);
delSql varchar(500);
begin
select
substr(max(high_value), 10, 10)
into
highvalue
from
all_tab_partitions
where
table_name =sTable_name
and table_owner=‘SYSDBA’ –语句中的table_owner可以修改为指定模式名
and HIGH_VALUE like ‘DATETIME%’;
–可以在此处定义保留分区子表数目,不包括默认分区子表
highvalue = ADD_MONTHS(highvalue,-13);
–print highvalue;
–语句中的table_owner可以修改为指定模式名
open del_part_name for select partition_name from all_tab_partitions where table_name=sTable_name and table_owner=‘SYSDBA’ and substr(high_value, 10, 10) <= highvalue and partition_name <> ‘P_299999’ order by 1;
loop
fetch del_part_name into partt_name;
–alter table sTable_name drop PARTITION partt_name;
delSql := 'alter table '|| sTable_name || ’ drop partition '||partt_name;
exit when del_part_name%NOTFOUND;
execute immediate delSql;
end loop;
close del_part_name;
end;
–3–
开始测试
–1>查看ET_AIRPORTCONTROL的分区子表信息
select partition_name from all_tab_partitions where table_name=‘ET_AIRPORTCONTROL’ and table_owner=‘SYSDBA’ order by 1;
/*
P_201505
P_201506
P_201507
P_201508
P_201509
P_201510
P_201511
P_201512
P_201601
P_201602
P_201603
P_201604
P_201605
P_201606
P_201607
P_201608
P_201609
P_201610
P_201611
P_201612
P_201701
P_201702
P_201703
P_201704
P_201705
P_201706
P_201707
P_299999
/
select count() from all_tab_partitions where table_name=‘ET_AIRPORTCONTROL’ and table_owner=‘SYSDBA’ order by 1;
/
28/
–2>调用删除分区存储过程，保留分区数可以在存储过程中进行定义
call SYSDBA.“DELPARTITION”(‘ET_AIRPORTCONTROL’);
–3>
查询ET_AIRPORTCONTROL分区表的现有子表及子表数目
select partition_name from all_tab_partitions where table_name=‘ET_AIRPORTCONTROL’ and table_owner=‘SYSDBA’ order by 1;
/*
P_201607
P_201608
P_201609
P_201610
P_201611
P_201612
P_201701
P_201702
P_201703
P_201704
P_201705
P_201706
P_201707
P_299999
/
select count() from all_tab_partitions where table_name=‘ET_AIRPORTCONTROL’ and table_owner=‘SYSDBA’ order by 1;
/
14/
–4>调用增加分区存储过程，默认一次增加一个分区
call SYSDBA.“ADDPARTITION”(‘ET_AIRPORTCONTROL’);
–5>查询ET_AIRPORTCONTROL分区表的现有子表及子表数目
select partition_name from all_tab_partitions where table_name=‘ET_AIRPORTCONTROL’ and table_owner=‘SYSDBA’ order by 1;
/*
P_201607
P_201608
P_201609
P_201610
P_201611
P_201612
P_201701
P_201702
P_201703
P_201704
P_201705
P_201706
P_201707
P_201708
P_299999
/
select count() from all_tab_partitions where table_name=‘ET_AIRPORTCONTROL’ and table_owner=‘SYSDBA’ order by 1;
/
15/
–6>备份待删除分区子表
–可以采用逻辑导入导出进行备份，但是要求待还原的分区子表必须存在，不能被drop掉，否则会默认在当前模式下创建普通表进行还原：
-1-dexp ET_AIRPORTCONTROL_P_201601子表
dexp USERID=SYSDBA/sysdb1:5236 FILE=ET_AIRPORTCONTROL_P_201601.dmp LOG=ET_AIRPORTCONTROL_P_201601_dexp.log TABLES=OETHIS_TV.ET_AIRPORTCONTROL_P_201601 directory=/dmdb/backup_hisopenet/test
-2-truncate table
select count(
) from OETHIS_TV.ET_AIRPORTCONTROL_P_201601;
/
16556
*/
truncate table OETHIS_TV.ET_AIRPORTCONTROL_P_201601;
select count(*) from OETHIS_TV.ET_AIRPORTCONTROL_P_201601;
/
0/
-3-dimp ET_AIRPORTCONTROL_P_201601
子表
dimp USERID=SYSDBA/sysdb1:5236 FILE=ET_AIRPORTCONTROL_P_201601.dmp LOG=ET_AIRPORTCONTROL_P_201601_dimp.log TABLES=OETHIS_TV.ET_AIRPORTCONTROL_P_201601 directory=/dmdb/backup_hisopenet/test TABLE_EXISTS_ACTION=append EXCLUDE=INDEXES
–这里需要忽略建表错误以及创建索引错误，可以指定TABLE_EXISTS_ACTION=append EXCLUDE=INDEXES参数
-4-
校验数据是否正确，索引是否失效
select count(
) from OETHIS_TV.ET_AIRPORTCONTROL_P_201601;
/
16556
*/
select name,valid from “SYSOBJECTS” where valid =‘N’;
–NULL
评论前必须登录！
注册