对分区表批量添加分区的案例

分区表对分区操作的基本语句如下：

#增加Range表分区
alter table t_partition_range  add partition p5 values less than (TO_DATE('2017-10-01', 'yyyy-mm-dd'))；
#修改Range表分区
alter table t_partition_range  modifypartition p5 values less than (TO_DATE('2017-08-01', 'yyyy-mm-dd'))；
#删除Range表分区
alter table t_partition_range  drop partition p_5;

 

但是我们再工作中不可能一个表一个表的操作，就算你真的有这个毅力，过几个月，又要加分区了，你还要再来一遍吗 ？对于这种繁复的操作，我目前采用的方法是 直接写一个存储过程，等到要加的时候，只要输入参数（日期），需要添加分区的表，全部自动创建对应参数的日期。

看一个例子：

反洗钱项目又193张分区表，上个负责的同事跑路了，没有交接，现在我需要对193张分区表，按照天，加1个月的分区。

我肯定不可能一个一个的写，就算我这次写了，下个月我还要再加一遍，疯了 。如上分所说，我准备写一个存储过程。如下：

CREATE OR REPLACE PROCEDURE pro_alter_aml_boot( V_BUSS_DATE IN VARCHAR2,      --分区日期
                                                                                                   O_CODE OUT VARCHAR2,           --返回分区日期+1
                                                                                                   O_MESSAGE OUT VARCHAR2 )       --返回拼接语句
IS

/**********************************************************************************
*功     能  ：给反洗钱的表增加分区
*业务范围   ：
*作    者   ：
*创建日期   ：2010-07-25
*修 改 人   ：
*修改日期   ：
*修改批次   ：
***********************************************************************************/

     BUSS_DATE VARCHAR2(40);--用来接收传入日期
      V_TEMPDATE       VARCHAR2(1024);--接收日期+1
       V_STRSQL         VARCHAR2(1024);--用来接收拼接的alter语句

BEGIN
    BUSS_DATE := V_BUSS_DATE;
    
    --传入日期加1
      select to_char(to_date(BUSS_DATE,'yyyy-mm-dd hh24:mi:ss')+1,'yyyy-mm-dd hh24:mi:ss') into V_TEMPDATE from dual;
    --  dbms_output.put_line(V_TEMPDATE);--输出打印 
    O_CODE := V_TEMPDATE;--输出查看
    
    --循环遍历aml全量表，获取194张表名 这里很关键 不要使用DBA的表 权限不足 无法获取，使用user下的表可以
    FOR DJ IN (select  a.table_name from tybs.aml_sum a 
             ORDER BY a.table_name) LOOP
                      
     V_STRSQL := 'ALTER TABLE '|| DJ.table_name ||' ADD PARTITION PT_' ||BUSS_DATE ||'
      values less than (to_date('''||V_TEMPDATE||''','||'''yyyy-mm-dd hh24:mi:ss'''||')) /*tablespace TBS_YW_DATA*/';

     O_MESSAGE := V_STRSQL;
      EXECUTE IMMEDIATE V_STRSQL;
              
    END LOOP;
    
    --不要忘记循环，入参，变量等地方的分号
    --拼接时候不要忘记空格 拼接的语句下面一定要输出看看 否则很难调试

  
EXCEPTION
    WHEN OTHERS THEN
    ROLLBACK;
    O_CODE := 'ORA'||SQLCODE;
    O_MESSAGE := '[ERROR]:'||SUBSTR( SQLERRM, 1, 290 );
    --Exception Log Record
    RAISE;

END pro_alter_aml_boot;   

好了 ，写完这个以后 我只要输入一个要创建的分区日期，193张就自动完成。搞定

 

再分享一个自动创建分区的办法：

就是再创建表的时候，让ORACLE 自动创建，以后都交给ORACLE 自动管理 ，但是对于已经存在的表，那只能删除重建了。create table TYBS.EAST_DATA_ASLB_2
(
  corpcode VARCHAR2(32 CHAR) not null,
  fsssno   VARCHAR2(30 CHAR),
  filino   VARCHAR2(30 CHAR),
  brchno   VARCHAR2(30 CHAR) not null,
  itemcd   VARCHAR2(20 CHAR) not null,
  itemna   VARCHAR2(60 CHAR),
  itembl   NUMBER(22,2),
  trandt   DATE not null,
  crcycd   VARCHAR2(3 CHAR) not null,
  areana   VARCHAR2(60 CHAR),
  areacd   VARCHAR2(64 CHAR),
  reptdt   DATE not null,
  geldtp   VARCHAR2(12 CHAR)
)
tablespace TBS_TYBS_DATA
partition by range(reptdt) interval(numtodsinterval(1,'month'))
(
PARTITION p_1 values less than (to_date('2018-01-01','yyyy-mm-dd')) 
tablespace TBS_TYBS_DATA
)；