oracle-----分区表增加表空间

create or replace procedure proc_add_partition is
  v_sql_string VARCHAR2(2000);
begin
  for cur_part in (select distinct u.table_name,
                                   max(p.partition_name) max_part_name,
                                   regexp_substr(max(p.partition_name),
                                                 '[0-9]+') part --分区最大时间
                     from user_tables u, user_tab_partitions p
                    where u.table_name = p.table_name
                        and u.TABLE_NAME = 'tmp'--你要增加分区的表
                      and u.partitioned = 'YES'
                    group by u.table_name) loop
    for lo_date in (select to_char((to_date(cur_part.part, 'yyyy/MM/dd')) +
                                   (rownum),
                                   'yyyymmdd') s_date
                      from dual
                    connect by rownum <=
                               (trunc(to_date(cur_part.part, 'yyyy/MM/dd')) -
                               trunc(to_date(cur_part.part, 'yyyy/MM/dd') - 60))) loop
      v_sql_string := 'alter table ' || cur_part.table_name ||
                      ' add partition P' || lo_date.s_date ||
                      ' values less than ( to_date(''' || lo_date.s_date ||
                      ''',''yyyymmdd'')) ';
      execute immediate v_sql_string;
    end loop;
  end loop;
  commit;
end proc_add_partition;