Oracle 分区维护（使用procedure ）

最新推荐文章于 2025-08-13 22:02:05 发布

最新推荐文章于 2025-08-13 22:02:05 发布 · 148 阅读

文章标签：

#数据库

Oracle 专栏收录该内容

22 篇文章

订阅专栏

本文提供了一个用于Oracle数据库中表分区维护的PL/SQL过程示例。该过程能够自动为指定表创建当前月份的分区，并定期将过期数据迁移至备份表分区中，同时删除旧分区，以实现数据的有效管理。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >


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
    )
);