--执行sql
declare
v_sql varchar2(32767);
v_partitionName VARCHAR2(100);
v_highvalue DATE;
BEGIN
FOR cur IN ( select table_name,high_value from (
SELECT table_name
FROM user_tab_partitions
where table_name IN ('xxxx'
)
group by table_name
) a,( SELECT trunc(SYSDATE-64,'DD') +rownum/24*1 high_value FROM dual CONNECT BY ROWNUM <= 24*70)
)
LOOP
BEGIN
v_partitionName := to_char(cur.high_value,'"P"YYYYMMDDHH24');
v_sql := 'ALTER TABLE ' || cur.table_name
||' ADD PARTITION ' || v_partitionName
||' VALUES LESS THAN (to_date('''|| to_char(cur.high_value,'YYYY-MM-DD HH24') ||''',''YYYY-MM-DD HH24''))'
||' TABLESPACE xxxxx'||to_char(cur.high_value - INTERVAL '10' MINUTE,'DDD');
-- dbms_output.put_line(v_sql);
EXECUTE IMMEDIATE (v_sql);
exception WHEN OTHERS THEN NULL;
END;
END LOOP;
END;
手工增加分区
最新推荐文章于 2022-03-07 10:45:01 发布