I have a table that I converted to interval daily PARTITIONs.
Ie
partition by range (date_col) interval (numtoyminterval(1, 'DAY')) (partition P_OLDDATA values less than (to_date('01-JAN-2021','DD-MON-YYYY')) ); My issue is that there were some developers who referenced the PARTITION by name, which is a bad practice. Now that they changed to the format SYS_#### Is there some script I can run to rename the partitions to a meaningful name ie P_MMDDYYYY. I know the name should be innocuous but I'm getting blow back from developers.
Here's some code from a partition maintenance routine I wrote a while back... It should get you started. I used to schedule it nightly to look for intervals and clean them up
declare
l_high_value varchar2(4000);
l_partition_boundary date;
l_parname varchar2(40);
l_ddl varchar2(4000);
begin
for i in (
select a.owner, a.table_name, a.partitioning_type, t.partition_name, t.high_value, a.interval
from all_part_tables a, all_tab_partitions t
where a.owner = ...
and a.partitioning_type = 'RANGE'
and a.table_name not like 'BIN$%'
and a.interval is not null
and t.table_owner = a.owner
and t.table_name = a.table_name
and regexp_like(t.partition_name,'^SYS_P[[:digit:]]{1,10}') )
loop
begin
l_high_value := i.high_value;
execute immediate 'select '||l_high_value||'-1 from dual' into l_partition_boundary;
if upper(i.interval) like 'NUMTODSINTERVAL%' then
l_parname := substr(i.table_name,1,21)||'_'||to_char(l_partition_boundary,'YYYYMMDD');
elsif upper(i.interval) like 'NUMTOYMINTERVAL%MONTH%' then
l_parname := substr(i.table_name,1,23)||'_'||to_char(l_partition_boundary,'YYYYMM');
elsif upper(i.interval) like 'NUMTOYMINTERVAL%YEAR%' then
l_parname := substr(i.table_name,1,25)||'_'||to_char(l_partition_boundary,'YYYY');
elsif upper(i.interval) like 'INTERVAL''%''DAY%' then
l_parname := substr(i.table_name,1,21)||'_'||to_char(l_partition_boundary,'YYYYMMDD');
elsif upper(i.interval) like 'INTERVAL''%''MONTH%' then
l_parname := substr(i.table_name,1,23)||'_'||to_char(l_partition_boundary,'YYYYMM');
elsif upper(i.interval) like 'INTERVAL''%''YEAR%' then
l_parname := substr(i.table_name,1,25)||'_'||to_char(l_partition_boundary,'YYYY');
else
l_parname := null;
dbms_output.put_line ('Cannot determine correct partition name for table ' || i.table_name || ' partition ' || i.partition_name);
end if;
exception
when others then
l_parname := null;
dbms_output.put_line ('Cannot determine correct partition name for table ' || i.table_name || ' partition ' || i.partition_name||' hi='||l_high_value);
end;
if (l_parname is not null) then
l_ddl := 'alter table '||i.owner||'.'||i.table_name||' rename partition '||i.partition_name||' to '||l_parname;
dbms_output.put_line(l_ddl);
begin
execute immediate l_ddl;
exception
when others then
dbms_output.put_line('Failed with '||sqlerrm);
end;
end if;
end loop;
--
-- then we can use those new partition names to do the same for indexes
--
for i in (
with tabpar as (
select /*+ materialize */
a.owner, a.table_name, a.partitioning_type, t.partition_name, t.partition_position
from all_part_tables a, all_tab_partitions t
where a.owner = ...
and a.partitioning_type = 'RANGE'
and a.table_name not like 'BIN$%'
and a.interval is not null
and t.table_owner = a.owner
and t.table_name = a.table_name
), indpar as (
select /*+ materialize */
ai.owner, ai.table_name, ai.index_name, i.partition_name, i.partition_position
from all_part_indexes ai, all_ind_partitions i
where ai.owner = ...
and ai.partitioning_type = 'RANGE'
and ai.table_name not like 'BIN$%'
and ai.interval is not null
and i.index_owner = ai.owner
and ai.index_name = i.index_name
and ai.locality = 'LOCAL'
)
select t.owner, t.table_name, t.partition_name new_par_name, i.index_name, i.partition_name
from tabpar t, indpar i
where i.owner = t.owner
and i.table_name = t.table_name
and i.partition_position = t.partition_position
and not regexp_like(t.partition_name,'^SYS_P[[:digit:]]{1,10}')
and regexp_like(i.partition_name,'^SYS_P[[:digit:]]{1,10}')
)
loop
l_ddl := 'alter index '||i.owner||'.'||i.index_name||' rename partition '||i.partition_name||' to '||i.new_par_name;
dbms_output.put_line(l_ddl);
begin
execute immediate l_ddl;
exception
when others then
dbms_output.put_line('Failed with '||sqlerrm);
end;
end loop;
end;
/