oracle 分区基自动扩展,Oracle的用户表自动增加分区

CREATE OR REPLACE PROCEDURE guan_add_partition

--为一个用户下所有分区表自动增加分区.分区的列为date类型,分区名类似:p200706.

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 users';

EXECUTE IMMEDIATE v_sql_string;

ELSE

NULL;

END IF;

END LOOP;

CLOSE cur_part;

END;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29578568/viewspace-2138081/，如需转载，请注明出处，否则将追究法律责任。