達夢數據庫 分區表如何定時自動新增和刪除子分區

下面為自動刪除、創建分區子表的存儲過程和維護分區表的測試示例：

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