linux 分区表删除,管理分区表（自动创建和删除子分区）

我管理的数据库是一个数据仓库系统，数据量比较大，对一些系统ETL产生的中间表打算只保存半年，因此我设计了一个程序来帮助我管理我的分区表。他会自动创建子分区，并且把半年以前的子分区删除。

------------------------自动创建子分区的存储过程----------------------------

CREATE OR REPLACE PROCEDURE GENE_AUTO_ADD_PARTITION(part_month        varchar2)

IS

/***********************************************************************

/* Procedure   : GENE_AUTO_ADD_PARTITION

/* Description : General类程序，用于自动增加分区表的分区 == 计算到part_month为止，该增加多少个分区

/* Logic       : 1、找出需要增加分区的分区表，以及这些分区表的最近分区

/*               2、计算这些分区表要增加几个分区

/*               3、循环为这些分区表增加分区，直至增加到part_month分区为止

/*               4、记录日志

/* Warning     : 确保有足够权限，需显示授权：Grant select on dba_segments to user;

/* eg          : call gene_auto_add_partition(201210);

/* Author       YYYY-MM-DD      DSC

/* Feng         2012-07-30      初始版本

/*

/*********************************************************************/

vcurrPartitionName                 varchar2(100);

vhistPartitionName                 varchar2(100);

vCount                             number;

vDDLSql                            varchar2(1024);

vLessthan                          varchar2(24);

i1                                 number;

i2                                 number;

BEGIN

--format current partition name

vcurrPartitionName := 'M_'||part_month;

For x in (

SELECT segment_name , max(partition_name) partition_name, max(tablespace_name) tablespace_name

FROM dba_segments a

WHERE wner = 'BAM'

AND segment_type = 'TABLE PARTITION'

AND segment_name not like '%BIN%'

AND not exists (

SELECT 1

FROM dba_segments b

WHERE a.segment_name = b.segment_name

AND b.partition_name = vcurrPartitionName)

GROUP BY a.segment_name

) LOOP

--计算需要增加几个分区

SELECT (extract(year from to_date(part_month,'yyyymm')) - extract(year from to_date(substr(x.partition_name,3),'yyyymm')))*12 +

(extract(month from to_date(part_month,'yyyymm')) - extract(month from to_date(substr(x.partition_name,3),'yyyymm')))

INTO vCount

FROM dual;

For j in 1.. vCount LOOP

--get history Partition name

vhistPartitionName := 'M_'||to_char(add_months(to_date(substr(x.partition_name,3),'yyyymm'),j),'yyyymm');

vLessthan := to_char(add_months(to_date(substr(x.partition_name,3),'yyyymm'),j),'yyyymmdd');

vDDLSql := 'ALTER TABLE '||x.segment_name||' ADD PARTITION '||vhistPartitionName||' VALUES LESS THAN('||vLessthan||') TABLESPACE '||x.tablespace_name;

DBMS_UTILITY.exec_ddl_statement(vDDLSql);

i1:= i1 + 1;

END LOOP;

i1 := i1;

i2 := i2 + 1;

END LOOP;

raise_log('GENE_AUTO_ADD_PARTITION','为'||part_month||'创建子分区成功，共创建了'||i2||'张表的'||i1||'个分区');

EXCEPTION

WHEN others THEN

raise_application_err(SQLCODE,'GENE_AUTO_ADD_PARTITION',SQLERRM);

END;

------------------------自动删除子分区的存储过程----------------------------

CREATE OR REPLACE PROCEDURE GENE_AUTO_DROP_PARTITION(part_month      varchar2

,period          number default 6)

IS

/***********************************************************************

/* Procedure   : GENE_AUTO_DROP_PARTITION

/* Description : General类程序，用于自动删除分区表的分区

/* Logic       : 1、找出需要删除分区的分区表，以及这些分区表的最远分区,确保只删除bam_cur_dat, bam_plf_dat表空间中的分区表

/*               2、以period为界限，计算这些分区表要删除几个分区

/*               3、循环为这些分区表删除分区，直至删除到part_month分区为止

/*               4、检查表索引的状态，对于失效的索引进行重新分析。

/*               5、记录日志

/* Warning     : 确保有足够权限，需显示授权：Grant select on dba_segments to user;

/*               分区索引(local)不存在删除索引后索引失效的问题，因此在此不考虑索引失效的rebuild问题。

/*               由于数据仓库事实表中极少用到主外键约束，因此在此不考虑暂时停用约束和启用约束的问题。

/*               所删除的分区采用直接丢弃方式，因此在此不考虑删除分区前，对分区的数据进行备份。

/* eg          : call gene_auto_drop_partition(201210);

/*               call gene_auto_drop_partition(201210,12);

/* Author       YYYY-MM-DD      DSC

/* Feng         2012-07-31      初始版本

/*

/*********************************************************************/

vPartMonth                             varchar2(100);

vOldPartitionName                      varchar2(100);

vhistPartitionName                     varchar2(100);

vCount                                 number;

vDDLSql                                varchar2(1024);

i                                      number;

i2                                     number;

BEGIN

--format Old partition name

vPartMonth := to_char(add_months(to_date(part_month,'yyyymm'),-period),'yyyymm');

vOldPartitionName := 'M_'||vPartMonth;

dbms_output.put_line(vOldPartitionName);

i := 0;

i2:= 0;

For x in (

SELECT segment_name , min(partition_name) partition_name, min(tablespace_name) tablespace_name

FROM dba_segments a

WHERE wner = 'BAM'

AND segment_type = 'TABLE PARTITION'

AND segment_name not like '%BIN%'

AND tablespace_name = any('BAM_CUR_DAT','BAM_PLF_DAT')

AND exists (

SELECT 1

FROM dba_segments b

WHERE a.segment_name = b.segment_name

AND b.partition_name = vOldPartitionName)

GROUP BY a.segment_name

) LOOP

--计算要删除几个分区

SELECT (extract(year from to_date(vPartMonth,'yyyymm')) - extract(year from to_date(substr(x.partition_name,3),'yyyymm')))*12 +

(extract(month from to_date(vPartMonth,'yyyymm')) - extract(month from to_date(substr(x.partition_name,3),'yyyymm')))

INTO vCount

FROM dual;

For j in 0.. vCount LOOP

--get history partition name

vhistPartitionName := 'M_'||to_char(add_months(to_date(substr(x.partition_name,3),'yyyymm'),j),'yyyymm');

vDDLSql := 'ALTER TABLE '||x.segment_name||' DROP PARTITION '||vhistPartitionName;

i := i + 1;

raise_log('GENE_AUTO_DROP_PARTITION','删除'||x.segment_name||'表分区成功!删除了'||vhistPartitionName);

DBMS_UTILITY.exec_ddl_statement(vDDLSql);

END LOOP;

i := i ;

i2 := i2 + 1;

END LOOP;

raise_log('GENE_AUTO_DROP_PARTITION','删除'||vPartMonth||'之前的分区成功，删除了'||i2||'张表的'||i||'个分区。');

EXCEPTION

WHEN others THEN

raise_application_err(SQLCODE,'GENE_AUTO_DROP_PARTITION',SQLERRM);

END GENE_AUTO_DROP_PARTITION;