1. 首页
  2. IT资讯

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

主题测试文章,只做测试使用。发布者:布吉卡,转转请注明出处:http://www.cxybcw.com/195186.html

联系我们

13687733322

在线咨询:点击这里给我发消息

邮件:1877088071@qq.com

工作时间:周一至周五,9:30-18:30,节假日休息

QR code