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/,如需转载,请注明出处,否则将追究法律责任。