Skip to Main Content


Dev Live Dev Intro

This month we are celebrating Developers at AskTOM. We welcome Developers of all levels of experience to join us at our FREE Developer Live events coming in August. Just click on the left to register today! If you are brand new to Database Technology, then we also have got you covered. Just click on the right for your comprehensive FREE training program to kick start your Oracle Database Development journey!

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

    l_stamp_clause varchar2(200);
    l_new_parname  varchar2(200);
    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;

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


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