達(dá)夢(mèng)數(shù)據(jù)庫(kù) 分區(qū)表如何定時(shí)自動(dòng)新增和刪除子分區(qū)

下面為自動(dòng)刪除、創(chuàng)建分區(qū)子表的存儲(chǔ)過(guò)程和維護(hù)分區(qū)表的測(cè)試示例：

1 、每月定時(shí)增加一個(gè)分區(qū)子表
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 := ‘a(chǎn)lter 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 、每月定時(shí)刪除一個(gè)最 久 分區(qū)
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 、備份分區(qū)
可以采用邏輯導(dǎo)入導(dǎo)出進(jìn)行備份，但是前提需要保證待還原的分區(qū)子表沒(méi)有被drop掉，否則會(huì)在當(dāng)前模式下創(chuàng)建普通表進(jìn)行還原。

4 、回收空間
1>可以采用truncate table or partition_name 及時(shí)釋放單表或單個(gè)分區(qū)子表的磁盤(pán)空間；delete和drop操作并不能及時(shí)釋放磁盤(pán)空間，需要重新啟動(dòng)數(shù)據(jù)庫(kù)；
2>可以采用手動(dòng)清空undo段釋放磁盤(pán)空間，存儲(chǔ)過(guò)程如下：
create or replace procedure remove_space()
as
begin
EXECUTE IMMEDIATE ‘a(chǎn)lter system set ‘‘UNDO_RETENTION’’=1;’;
dbms_lock.sleep(15);–時(shí)間可以根據(jù)實(shí)際情況進(jìn)行設(shè)定
EXECUTE IMMEDIATE ‘a(chǎn)lter system set ‘‘UNDO_RETENTION’’=900;’;
end;

5 、測(cè)試示例：
–1--創(chuàng)建測(cè)試的分區(qū)表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-- 創(chuàng)建存儲(chǔ)過(guò)程
–1>定時(shí)增加分區(qū)
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’ —語(yǔ)句中的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 := ‘a(chǎn)lter 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> 定時(shí)刪除分區(qū)
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’ --語(yǔ)句中的table_owner可以修改為指定模式名
and HIGH_VALUE like ‘DATETIME%’;
–可以在此處定義保留分區(qū)子表數(shù)目,不包括默認(rèn)分區(qū)子表
highvalue = ADD_MONTHS(highvalue,-13);
–print highvalue;
–語(yǔ)句中的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-- 開(kāi)始測(cè)試
–1>查看ET_AIRPORTCONTROL的分區(qū)子表信息
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>調(diào)用刪除分區(qū)存儲(chǔ)過(guò)程，保留分區(qū)數(shù)可以在存儲(chǔ)過(guò)程中進(jìn)行定義
call SYSDBA.“DELPARTITION”(‘ET_AIRPORTCONTROL’);

–3> 查詢(xún)ET_AIRPORTCONTROL分區(qū)表的現(xiàn)有子表及子表數(shù)目
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>調(diào)用增加分區(qū)存儲(chǔ)過(guò)程，默認(rèn)一次增加一個(gè)分區(qū)
call SYSDBA.“ADDPARTITION”(‘ET_AIRPORTCONTROL’);
–5>查詢(xún)ET_AIRPORTCONTROL分區(qū)表的現(xiàn)有子表及子表數(shù)目
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>備份待刪除分區(qū)子表
–可以采用邏輯導(dǎo)入導(dǎo)出進(jìn)行備份，但是要求待還原的分區(qū)子表必須存在，不能被drop掉，否則會(huì)默認(rèn)在當(dāng)前模式下創(chuàng)建普通表進(jìn)行還原：
-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
–這里需要忽略建表錯(cuò)誤以及創(chuàng)建索引錯(cuò)誤，可以指定TABLE_EXISTS_ACTION=append EXCLUDE=INDEXES參數(shù)

-4- 校驗(yàn)數(shù)據(jù)是否正確，索引是否失效
select count( ) from OETHIS_TV.ET_AIRPORTCONTROL_P_201601;
/
16556
*/
select name,valid from “SYSOBJECTS” where valid =‘N’;
–NULL