BEGIN v_prc:='PRC_ADD_PARTITION_DAY'; SELECT to_char(SYSDATE,'yyyymmdd') INTO v_date FROM dual; --正文SQL处理
-- 1 拿出需要拓展月分区的表的相关信息 FOR v_num IN 1..63 LOOP OPEN v_open_cur FOR 'SELECT TABLE_NAME,TABLE_OWNER,MAX(SUBSTR(PARTITION_NAME,2,8)),TABLESPACE_NAME FROM ALL_TAB_PARTITIONS WHERE TABLE_OWNER=''XXX'' --AND TABLE_NAME LIKE ''MD%'' --例如某一类以MD开头的表 AND LENGTH(RTRIM(PARTITION_NAME))=9 AND table_name NOT LIKE ''%$%'' GROUP BY TABLE_NAME,TABLE_OWNER,TABLESPACE_NAME';
-- 开始把值赋给变量 准备循环迭代了 LOOP FETCH v_open_cur INTO v_table_name,v_table_owner,v_max_part_mon,v_tablespace_name; EXIT WHEN v_open_cur%NOTFOUND;
-- 把循环里面的最大day加1 作为下一个天 为开始拓展表分区做好准备 SELECT to_char(to_date(v_max_part_mon,'YYYYMMDD')+1,'YYYYMMDD') INTO v_max_next_mon FROM DUAL; SELECT to_char(to_date(v_max_part_mon,'YYYYMMDD')+2,'YYYYMMDD') INTO v_max_next2_mon FROM DUAL;
-- 开始拓展表空间 v_stepnum :=v_stepnum+1; if (v_table_name!='D_CHN_G_CEILL_KPI') AND (v_table_name!='D_CHN_W_CEILL_KPI') then ----排除 表D_CHN_G_CEILL_KPI v_sql:= 'ALTER TABLE '||v_table_owner||'.'||v_table_name||' ADD PARTITION P'||v_max_next_mon||' VALUES LESS THAN ('''||v_max_next2_mon||''') TABLESPACE '||v_tablespace_name||' '; EXECUTE IMMEDIATE v_sql; PRC_LOG_WRT(v_date,v_prc,v_stepnum,v_sql,v_errmsg,SQL%ROWCOUNT); COMMIT;
ELSIF (v_table_name='D_CHN_G_CEILL_KPI') OR (v_table_name='D_CHN_W_CEILL_KPI') then v_sql:= 'ALTER TABLE '||v_table_owner||'.'||v_table_name||' ADD PARTITION P'||v_max_next_mon||' VALUES LESS THAN (TO_DATE('''||v_max_next2_mon||''',''YYYYMMDD'',''NLS_CALENDAR=GREGORIAN'')) TABLESPACE '||v_tablespace_name; EXECUTE IMMEDIATE v_sql; PRC_LOG_WRT(v_date,v_prc,v_stepnum,v_sql,v_errmsg,SQL%ROWCOUNT); COMMIT;