Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, ali.

Asked: May 25, 2020 - 10:21 am UTC

Answered by: Connor McDonald - Last updated: May 26, 2020 - 5:58 am UTC

Category: Database Administration - Version: 19c

Viewed 100+ 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 we 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.

More to Explore

VLDB

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