欢迎光临
我们一直在努力

达梦数据库 分区表如何定时自动新增和删除子分区 - 数据库

下面为自动删除、创建分区子表的存储过程和维护分区表的测试示例:

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

赞(0)
【声明】:本博客不参与任何交易,也非中介,仅记录个人感兴趣的主机测评结果和优惠活动,内容均不作直接、间接、法定、约定的保证。访问本博客请务必遵守有关互联网的相关法律、规定与规则。一旦您访问本博客,即表示您已经知晓并接受了此声明通告。