oracle按dt字段建立分区,oracle一卡通消费数据分区表建立及自动建立分区存储过程...

创建存储过程自动建立到当前月份的分区，并写入日志。

存储过程创建：

CREATE OR REPLACE PROCEDURE GENE_AUTO_ADD_PARTITION

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;

/* Author YYYY-MM-DD DSC

/* lihan 2015-04-24 初始版本

/*

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

part_month varchar2(100);

vcurrPartitionName varchar2(100);

vhistPartitionName varchar2(100);

vCount number;

vDDLSql varchar2(1024);

vLessthan varchar2(100);

i1 number;

i2 number;

BEGIN

--初始化参数值

part_month :=to_char(sysdate + 90,'yyyymm');--可提前新建三个月后的分区，预留问题发现及修复时间

i1 :=0;

--format current partition name

vcurrPartitionName := 'p'||part_month;

For x in (

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

FROM dba_segments a

WHERE owner = 'YBG_CP'

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,2,6),'yyyymm')))*12 +

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

INTO vCount

FROM dual;

For j in 1..vCount LOOP

--get history Partition name

vhistPartitionName := 'p'||to_char(add_months(to_date(substr(x.partition_name,2,6),'yyyymm'),j),'yyyymm');

vLessthan := to_char(add_months(to_date(substr(x.partition_name,2,6)||'01','yyyy-mm-dd hh24:mi:ss'),j+1),'yyyy-mm-dd hh24:mi:ss');

vDDLSql := 'ALTER TABLE M_REC_CONSUME 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;

insert into m_rec_consume_log(updtime,prc_name,dscp)values(sysdate,'GENE_AUTO_ADD_PARTITION','为M_REC_CONSUME创建三个月后子分区成功，共创建了'||i1||'个分区');

commit;

EXCEPTION

WHEN others THEN

raise;

rollback;

insert into m_rec_consume_log(updtime,prc_name,dscp)values(sysdate,'GENE_AUTO_ADD_PARTITION','更新分区失败！');

END; 还需创建一个日志表：

create table m_rec_consume_log

(

updtime date,

prc_name varchar2(1000),

dscp varchar2(1000)

)