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;
oracle-----分区表增加表空间
最新推荐文章于 2023-04-18 18:29:35 发布