自动创建分区

CREATE OR REPLACE PROCEDURE guan_add_partition
/*
/*为一个用户下所有分区表自动增加分区.分区的列为date类型,分区名类似:p200706.
/*create by David
*/
AS
   v_table_name       VARCHAR2 (50);
   v_partition_name   VARCHAR2 (50);
   v_month            CHAR (6);
   v_add_month_1      CHAR (6);
   v_sql_string       VARCHAR2 (2000);
   v_add_month        VARCHAR2 (20);

   CURSOR cur_part
   IS
      SELECT DISTINCT u.table_name, MAX (p.partition_name) max_part_name
                 FROM user_tables u, user_tab_partitions p
                WHERE u.table_name = p.table_name AND u.partitioned = 'YES'
             GROUP BY u.table_name;
BEGIN
   SELECT TO_CHAR (SYSDATE, 'yyyymm')
     INTO v_month
     FROM DUAL;

   SELECT TO_CHAR (ADD_MONTHS (SYSDATE, 1), 'yyyymm')
     INTO v_add_month_1
     FROM DUAL;

   SELECT TO_CHAR (ADD_MONTHS (TRUNC (SYSDATE, 'mm'), 2), 'yyyy-mm-dd')
     INTO v_add_month
     FROM DUAL;

   OPEN cur_part;

   LOOP
      FETCH cur_part
       INTO v_table_name, v_partition_name;

      EXIT WHEN cur_part%NOTFOUND;

      IF TO_NUMBER (SUBSTR (v_partition_name, 2)) <=
                                              TO_NUMBER (SUBSTR (v_month, 1))
      THEN
         v_sql_string :=
               'alter table '
            || v_table_name
            || ' add partition p'
            || v_add_month_1
            || ' VALUES LESS THAN ( to_date('''
            || v_add_month
            || ''',''yyyy-mm-dd'') ) tablespace ONLINE_INSPECT_SYSTEM';

         EXECUTE IMMEDIATE v_sql_string;
      ELSE
         NULL;
      END IF;
   END LOOP;

   CLOSE cur_part;
END;
/