oracle自动创建表分区

创建一个table，记录哪些表需要创建表分区

create table STAT_TABLE
(
  tablename          VARCHAR2(30),
  pre_partition_name VARCHAR2(30),
  tb_name            VARCHAR2(30),
  add_inteval        NUMBER,
  owner              VARCHAR2(32)
)
tablespace USERS
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );

定时执行存储过程

CREATE OR REPLACE PROCEDURE SP_ADD_PARTITION(statdate DATE) Authid Current_User AS

  V_CREATETB_SQL     VARCHAR2(1000);

  V_TB_CNT           NUMBER;
  V_PARTITION_CNT    NUMBER;
  V_STAT_DATE        DATE DEFAULT  statdate;
  V_STATDATE_STR     VARCHAR2(32) DEFAULT TO_CHAR(V_STAT_DATE, 'YYYYMMDD');
  V_PARTITION_NAME   VARCHAR2(100);
  V_TB_NAME_P        VARCHAR2(100);
  V_TB_NAME          VARCHAR2(100);
  V_DATAFILE         VARCHAR2(1000);
  V_SQL_CMD          VARCHAR2(1000);
  V_TABLENAME        VARCHAR2(100);
  V_SUB_PARTITION    NUMBER;

BEGIN
  FOR TAB IN (SELECT T.TABLENAME,
                     T.PRE_PARTITION_NAME,
                     T.TB_NAME,
                     T.ADD_INTEVAL,
                     T.OWNER
                FROM STAT_TABLE T
                 ) LOOP
    BEGIN
      V_PARTITION_NAME := TAB.PRE_PARTITION_NAME || V_STATDATE_STR;
      V_TB_NAME        := TAB.TB_NAME;
      V_TABLENAME      := TAB.TABLENAME;

      BEGIN
        /*判断表分区是否存在*/
        SELECT COUNT(1)
          INTO V_PARTITION_CNT
          FROM DBA_TAB_PARTITIONS t
         WHERE T.TABLE_NAME = upper(TAB.TABLENAME)
          AND T.PARTITION_NAME = V_PARTITION_NAME
          and T.TABLE_OWNER=upper(TAB.Owner);

        IF V_PARTITION_CNT = 0 THEN --判断是否需要创建子分区
            V_SQL_CMD := 'ALTER TABLE ' || tab.Owner ||'.' ||TAB.TABLENAME ||
                                ' ADD PARTITION ' || V_PARTITION_NAME ||
                                ' VALUES LESS THAN (to_date(''' ||
                                TO_CHAR(V_STAT_DATE + TAB.ADD_INTEVAL, 'YYYYMMDD') ||
                                ''',''yyyymmdd'')) TABLESPACE ' || V_TB_NAME ||' COMPRESS';
          EXECUTE IMMEDIATE V_SQL_CMD;
        END IF;
        EXCEPTION WHEN OTHERS THEN
                    dbms_output.put_line(V_SQL_CMD);
       END;


     EXCEPTION WHEN OTHERS THEN
          --sp_etl_error_logs_pro('SP_ADD_PARTITION', v_sql_cmd, V_STATDATE_STR);
          null;
    END;
  END LOOP;
END SP_ADD_PARTITION;