Connor and Chris will both be at AI World from October 12 to October 17 , the premier Oracle conference of 2025. If you're in Vegas, please come say Hi or pop into our sessions
Thanks for the question, ali.
Asked: May 25, 2020 - 10:21 am UTC
Last updated: May 26, 2020 - 5:58 am UTC
Version: 19c
Viewed 1000+ times
declare l_stamp_clause varchar2(200); l_new_parname varchar2(200); begin for i in ( select t.table_name, p.partition_name, p.high_value, t.owner from dba_part_tables t, dba_tab_partitions p where t.owner = ... and t.partitioning_type = 'RANGE' and t.interval is not null and t.owner = p.table_owner and t.table_name = p.table_name and p.partition_name like 'SYS\_P%' escape '\' and t.table_name not like 'SYS\_P%' escape '\' ) loop l_stamp_clause := i.high_value; execute immediate 'select to_char('||l_stamp_clause||',''YYYYMM'') from dual' into l_new_parname; l_new_parname := 'MEANINGFUL_NAME'||'_p'||l_new_parname; execute immediate 'alter table '||i.owner||'.'||i.table_name||' rename partition '||i.partition_name||' to '||l_new_parname; end loop; end;
If you are new to partitioning, check out Connor McDonald's introduction series here.
Documentation set on VLDB and Partitioning.