Oracle 分区维护（使用procedure ）

e351r72l26

create or replace procedure pro_maintain_partition( tab_name in  varchar2)
as
var_tab_name   varchar2(32) ;
var_tab_bak_name   varchar2(32) ;
var_partition      varchar2(32) ;
var_less_than      varchar2(8) ;
var_flag           number ;
begin
select upper(tab_name) into var_tab_name from dual ;
select upper(tab_name||'_bak') into var_tab_bak_name from dual ;
select count(1) into var_flag from user_segments
where segment_name = var_tab_name
and partition_name like '%'||to_char(sysdate , 'yyyy_mm') ;
select var_tab_name||'_'||to_char(sysdate , 'yyyy_mm') into var_partition from dual ;
select to_char(add_months(sysdate , 1) , 'yyyymm')||'01' into var_less_than from dual ;
if var_flag = 0 then
--如当月分区不存在，则新增当月分区
execute immediate 'alter table '||var_tab_name||' add partition '||var_partition||' values less than ('||var_less_than||')';
end if;
select count(1) into var_flag from user_segments
where segment_name = var_tab_bak_name
and partition_name like '%'||to_char(add_months(sysdate , -4) , 'yyyy_mm') ;
if var_flag = 0 then
select var_tab_bak_name||'_'||to_char(add_months(sysdate , -4) , 'yyyy_mm') into var_partition from dual ;
select to_char(add_months(sysdate , -3) , 'yyyymm')||'01' into var_less_than from dual ;
--如备份表分区不存在，则新增备份月分区
execute immediate 'alter table '||var_tab_bak_name||' add partition '||var_partition||' values less than ('||var_less_than||')';
--备份数据     
select var_tab_name||'_'||to_char(add_months(sysdate , -4) , 'yyyy_mm') into var_partition from dual ;
execute immediate 'insert into '||var_tab_bak_name||' select * from '||var_tab_name||' partition('||var_partition||')';
/*insert into var_tab_bak_name
select * from var_tab_name partition(var_partition) ;*/
commit ;
--删除已备份月分区     
execute immediate 'alter table '||var_tab_name||' drop partition '||var_partition;
end if;
end;



Table

-- Create table
create table TB_STEP_WAIT_HOLD
(
YYYYMMDD        VARCHAR2(16),
SHIFT           VARCHAR2(8),
STAGEID         VARCHAR2(64),
LOTID           VARCHAR2(16),
RECPNAME        VARCHAR2(8),
QTY             NUMBER,
TRACKINTIME     DATE,
TRACKOUTTIME    DATE,
STEP_START_TIME DATE,
Q_H_TIME        NUMBER,
END_TIME        DATE,
START_TIME      DATE,
UPDATE_TIME     DATE
)
partition by range (YYYYMMDD)
(
partition TB_STEP_WAIT_HOLD_2014_09 values less than ('20141001')
tablespace RPTCMT_DATA
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 10M
next 10M
minextents 1
maxextents unlimited
pctincrease 0
),
partition TB_STEP_WAIT_HOLD_2014_10 values less than ('20141101')
tablespace RPTCMT_DATA
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 10M
next 10M
minextents 1
maxextents unlimited
pctincrease 0
),
partition TB_STEP_WAIT_HOLD_2014_11 values less than ('20141201')
tablespace RPTCMT_DATA
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 10M
next 10M
minextents 1
maxextents unlimited
pctincrease 0
),
partition TB_STEP_WAIT_HOLD_2014_12 values less than ('20150101')
tablespace RPTCMT_DATA
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 10M
next 10M
minextents 1
maxextents unlimited
pctincrease 0
)
);


Bak Table

-- Create table
create table TB_STEP_WAIT_HOLD_BAK
(
YYYYMMDD        VARCHAR2(16),
SHIFT           VARCHAR2(8),
STAGEID         VARCHAR2(64),
LOTID           VARCHAR2(16),
RECPNAME        VARCHAR2(8),
QTY             NUMBER,
TRACKINTIME     DATE,
TRACKOUTTIME    DATE,
STEP_START_TIME DATE,
Q_H_TIME        NUMBER,
END_TIME        DATE,
START_TIME      DATE,
UPDATE_TIME     DATE
)
partition by range (YYYYMMDD)
(
partition TB_STEP_WAIT_HOLD_BAK_2014_08 values less than ('20140901')
tablespace RPTCMT_DATA
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 10M
next 10M
minextents 1
maxextents unlimited
pctincrease 0
)
);