Rename system generated partition names in Interval partition

There's a trick you can use, at least for date-range partitions: the HIGH_VALUE field in user_tab_partitions is a valid date expression. If you "eval" that, you'll get a date object for the cutoff point for that partition. You can then use that to build your partition name from.

Sample PL/SQL to try out (generates a string from the high_value column):

declare
  hv varchar2(9);
begin
  for x in (select partition_name, high_value
              from user_tab_partitions
              where table_name = 'FOO' and partition_name like 'SYS_%')
  loop
    execute immediate 'select to_char('||x.high_value||'-1,''YYYYMMDD'') from dual' into hv;
    execute immediate ('alter table foo rename partition '||x.partition_name ||' to PART_'||hv);
 
-- dbms_output.put_line('alter table foo rename partition '||x.partition_name ||' to PART_'||hv);
  end loop;
end;
/

=============================================

declare
v_table_name varchar2(30) := 'MYTAB';
v_hv_date    date;

function str_to_date(p_str in varchar2) return date is
v_date date;
v_sql  varchar2(4000);
begin
  select 'select cast(' || p_str || ' as date) from dual'
   into v_sql
  from dual;
 
 execute immediate v_sql into v_date;
 return v_date;
end str_to_date;

begin
  -- Rename table partitions
 for cur_parts in (
    with par_xml as (
       select dbms_xmlgen.getXMLType('select table_name,
                                             partition_name,
                                             high_value
                                        from user_tab_partitions
                                       where table_name = '''||v_table_name||'''
                                         and not(regexp_like(partition_name, ''^P[0-9]{8}$''))'
                      ) as col_xml from dual
            ),
       partitions as (select x.*
                        from par_xml p,
               xmltable('/ROWSET/ROW' passing p.col_xml columns
                    table_name varchar2(30) path '/ROW/TABLE_NAME',
                    partition_name varchar2(30) path '/ROW/PARTITION_NAME',
                    high_value varchar2(500) path '/ROW/HIGH_VALUE') x
               )
  select table_name, partition_name, high_value
   from partitions)
  loop
  v_hv_date := str_to_date(cur_parts.high_value) - 1;
  execute immediate 'alter table '||v_table_name||' rename partition '||cur_parts.partition_name||' to P'||to_char(v_hv_date, 'YYYYMMDD');
  end loop;

  -- Rename index partitions
  for cur_idx_parts in (
    with par_xml as (
       select dbms_xmlgen.getXMLType('select index_name,
                                             partition_name,
                                             high_value
                                        from user_ind_partitions
                                       where index_name in (select index_name from user_indexes where table_name = '''||v_table_name||''')
                                         and not(regexp_like(partition_name, ''^P[0-9]{8}$''))'
                      ) as col_xml from dual
            ),
       partitions as (select x.*
                        from par_xml p,
               xmltable('/ROWSET/ROW' passing p.col_xml columns
                    index_name varchar2(30) path '/ROW/INDEX_NAME',
                    partition_name varchar2(30) path '/ROW/PARTITION_NAME',
                    high_value varchar2(500) path '/ROW/HIGH_VALUE') x
               )
  select index_name, partition_name, high_value
    from partitions)
  loop
    v_hv_date := str_to_date(cur_idx_parts.high_value) - 1;
  execute immediate 'alter index '||cur_idx_parts.index_name||' rename partition '||cur_idx_parts.partition_name||' to P'||to_char(v_hv_date, 'YYYYMMDD');
  end loop;
end;
/