Oracle 11g 间隔分区(INTERVAL)批量规范命名

友情提示：请在业务空闲是操作。为什么呢？

--Oracle 11g 间隔分区(INTERVAL)重命名，批量生成rename分区脚本set linesize 180set serverout ondeclare  v_table_owner          varchar2(100) := 'OP%';  v_table_name_day_p     varchar2(100);  v_table_name_month_p   varchar2(100);  v_print                number:=1; --1:只打印执行脚本，2，DB直接执行  v_new_partition_name   varchar2(100);  v_sql                  varchar2(2000);  vSqlerrm               varchar2(256);begin  for i in (select t1.table_owner,       t2.table_name,       t1.partition_name,       t2.interval,       t1.high_value  from dba_tab_partitions t1,dba_part_tables t2  where t2.owner = t1.table_owner   and t2.table_name = t1.table_name   and t1.interval = 'YES'   and t2.partitioning_type='RANGE'   and t1.partition_name like 'SYS\_%' escape '\'   and table_owner like v_table_owner order by t1.table_owner,t1.partition_name) loop    if i.interval='NUMTODSINTERVAL(1,''DAY'')' then    v_table_name_day_p := substr(i.table_name, 1, 19);  --日分区取前19位字符；    execute immediate 'select to_char(' || i.high_value ||                      ' - NUMTODSINTERVAL(1,''DAY''), ''"' ||                      v_table_name_day_p || '"YYYY"M"MM"D"DD'') from dual'           into v_new_partition_name;    elsif i.interval='NUMTOYMINTERVAL(1,''MONTH'')'  then    v_table_name_month_p := substr(i.table_name, 1, 23); --月分区取前23位字符;    execute immediate 'select to_char(' || i.high_value ||                      ' - NUMTOYMINTERVAL(1,''MONTH''), ''"' ||                      v_table_name_month_p || '"YYYY"M"MM'') from dual'      into v_new_partition_name;    end if;    if v_print=1 then    dbms_output.put_line('alter table ' || i.table_owner || '.' ||i.table_name || ' rename partition ' ||i.partition_name || ' to ' || v_new_partition_name || ';');    elsif v_print=2 then        v_sql := 'alter table ' || i.table_owner || '.' ||i.table_name || ' rename partition ' ||i.partition_name || ' to ' || v_new_partition_name;        execute immediate v_sql;    end if;  end loop;exception  when others then    vSqlerrm := sqlerrm;    dbms_output.put_line(vSqlerrm||'|'||v_sql);end;