使用存储过程和dbms_job自动添加表分区

被添加分区的表必须初始化分区

CREATE TABLE t_test (
  spBillno VARCHAR2 ( 20 ) NOT NULL,
  orderId VARCHAR2 ( 50 ) NOT NULL,
  userId NUMBER ( 12 ),
  bankType CHAR ( 5 ),
  payMoney NUMBER ( 10 ) DEFAULT 0 NOT NULL,
  feeType CHAR ( 1 ),
  orderTime DATE,
  orderStatus CHAR ( 1 ),
  returnTime DATE,
  CONSTRAINT PK_t_test46 PRIMARY KEY (spBillno),
  CONSTRAINT TC_t_test18 UNIQUE (orderId)
)
partition BY range(orderTime)
(
      partition part_test_20050101 VALUES less than(to_date('20050101','yyyymmdd')) tablespace tbs_test_part,
      partition part_test_20050701 VALUES less than(to_date('20050701','yyyymmdd')) tablespace tbs_test_part,
      partition part_test_20060101 VALUES less than(to_date('20060101','yyyymmdd')) tablespace tbs_test_part,
      partition part_test_20060701 VALUES less than(to_date('20060701','yyyymmdd')) tablespace tbs_test_part,
      partition part_test_20070101 VALUES less than(to_date('20070101','yyyymmdd')) tablespace tbs_test_part,
      partition part_test_20070701 VALUES less than(to_date('20070701','yyyymmdd')) tablespace tbs_test_part,
      partition part_test_20080101 VALUES less than(to_date('20080101','yyyymmdd')) tablespace tbs_test_part,
      partition part_test_20080701 VALUES less than(to_date('20080701','yyyymmdd')) tablespace tbs_test_part,
      partition part_test_20090101 VALUES less than(to_date('20090101','yyyymmdd')) tablespace tbs_test_part,
      partition part_test_20090701 VALUES less than(to_date('20090701','yyyymmdd')) tablespace tbs_test_part,
      partition part_test_20100101 VALUES less than(to_date('20100101','yyyymmdd')) tablespace tbs_test_part,
      partition part_test_20100701 VALUES less than(to_date('20100701','yyyymmdd')) tablespace tbs_test_part,
      partition part_test_20110101 VALUES less than(to_date('20110101','yyyymmdd')) tablespace tbs_test_part,
      partition part_test_20110701 VALUES less than(to_date('20110701','yyyymmdd')) tablespace tbs_test_part,
      partition part_test_20120101 VALUES less than(to_date('20120101','yyyymmdd')) tablespace tbs_test_part,
      partition part_test_20120701 VALUES less than(to_date('20120701','yyyymmdd')) tablespace tbs_test_part,
      partition part_test_20130101 VALUES less than(to_date('20130101','yyyymmdd')) tablespace tbs_test_part,
      partition part_test_20130701 VALUES less than(to_date('20130701','yyyymmdd')) tablespace tbs_test_part,
      partition part_test_20140101 VALUES less than(to_date('20140101','yyyymmdd')) tablespace tbs_test_part,
      partition part_test_20140701 VALUES less than(to_date('20140701','yyyymmdd')) tablespace tbs_test_part,
      partition part_test_20150101 VALUES less than(to_date('20150101','yyyymmdd')) tablespace tbs_test_part,
      partition part_test_20150701 VALUES less than(to_date('20150701','yyyymmdd')) tablespace tbs_test_part
)
;

用于添加分区的存储过程

CREATE OR REPLACE PROCEDURE PRO_PARTITION
(
  tablename             VARCHAR2, ---要添加分区的表
  tableSpaceName        VARCHAR2 --表空间名 
) 
AS
  v_SqlExec             VARCHAR2(2000); --DDL语句变量 
  v_Partwareid1         NUMBER; --创建分区的wareid 
  v_err_num             NUMBER; --ORA错误号 
  v_err_msg             VARCHAR2(100); --错误描述 
  v_part_wareid_max     VARCHAR2(20); --tablename 表分区的最大wareid号 
  v_begin               NUMBER; ----字符串的开始位置 
  v_count               NUMBER; ----取多少个字符串 
  v_part_name           VARCHAR2(100); --要添加分区表的名称的前缀 
BEGIN
  SELECT to_char(sysdate, 'MMdd') INTO v_Partwareid1 FROM dual;

  --每年的0101或者0701添加分区
  IF v_Partwareid1 = 0701 OR v_Partwareid1 = 0101 THEN
  
    ----字符串的开始位置 
    SELECT INSTR(partition_name, '_', -1, 1) + 1
      INTO v_begin
      FROM user_tab_partitions
     WHERE TABLE_NAME = UPPER(tablename)
       AND rownum < 2;
  
    ----取多少个字符串 
    SELECT LENGTH(partition_name) - v_begin + 1
      INTO v_count
      FROM user_tab_partitions
     WHERE TABLE_NAME = UPPER(tablename)
       AND rownum < 2;
  
    --查询分区表tablename的最大wareid值 
    SELECT MAX(to_number(SUBSTR(partition_name, v_begin, v_count)))
      INTO v_part_wareid_max
      FROM user_tab_partitions
     WHERE TABLE_NAME = UPPER(tablename);
  
    ---计算分区表的名称 
    SELECT SUBSTR(partition_name, 1, v_begin - 1)
      INTO v_part_name
      FROM user_tab_partitions
     WHERE TABLE_NAME = UPPER(tablename)
       AND rownum < 2;
  
    SELECT to_char(add_months(sysdate, 1), 'yyyyMMdd')
      INTO v_Partwareid1
      FROM dual;
  
    v_SqlExec := 'ALTER TABLE ' || tablename || ' ADD PARTITION ' ||
                 v_part_name || v_Partwareid1 ||
                 ' values less than(to_date(' || v_Partwareid1 || ',' ||
                 '''yyyyMMdd''' || ')) TABLESPACE ' || TableSpaceName;
    dbms_output.put_line('created' || tablename || 'partition table' || '=' || v_SqlExec);
    DBMS_Utility.Exec_DDL_Statement(v_SqlExec);
  
  END IF;
EXCEPTION
  WHEN OTHERS THEN
    v_err_num := SQLCODE;
    v_err_msg := SUBSTR(SQLERRM, 1, 100);
    dbms_output.put_line('pro_partition execute failed,errcode=' ||
                         v_err_num || 'errmsg=' || v_err_msg);
    commit;
END PRO_PARTITION;

将存储过程放入job当中，定时执行

DECLARE
  v_JobID      INTEGER;
  v_tablespace varchar2(20);
  v_execTime   varchar2(20);
  
  --定义一个数组类型
  TYPE a_type IS TABLE OF VARCHAR2(50);
  tables_name a_type := a_type();
BEGIN
  --定义表空间

  v_tablespace := 'tbs_test_dat';

  --定义执行时间:凌晨00：00
  v_execTime := 'TRUNC(SYSDATE + 1)';

  --初始化数组长度

  tables_name.extend(3);

  --添加需要生成分区的表名
  tables_name(1) := 'T_TEST';
  tables_name(2) := 'T_TEST2';
  tables_name(3) := 'T_TEST3';

  --循环触发建立分区定时任务
  FOR I IN 1 .. tables_name.COUNT LOOP
    IF tables_name(i) IS NOT NULL THEN
    
      --DBMS_OUTPUT.put_line('pro_partition(''' || tables_name(i) || ''',''' || v_tablespace || ''');');
      dbms_job.submit(v_JobID,
                      'pro_partition(''' || tables_name(i) || ''',''' ||
                      v_tablespace || ''');',
                      sysdate,
                      v_execTime);
    END IF;
  END LOOP;

END;
/ 
COMMIT;