Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

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

You Asked

hi , is there any way to give a patern name to automatic partitions when using this DBMS_REDEFINITION.start_redef_table package.

thank you by advance.


and Connor said...

Unfortunately no, but once the operation is complete, you can rename partitions as you see fit.

A little plsql (pseudo code below) can be utilised to look at the definition, eg

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;


Just be a little careful renaming partitions whilst the table is in active use. You never who might be referring an existing name.

Is this answer out of date? If it is, please let us know via a Comment

More to Explore

VLDB

If you are new to partitioning, check out Connor McDonald's introduction series here.

VLDB

Documentation set on VLDB and Partitioning.