Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Paul.

Asked: November 10, 2021 - 9:34 am UTC

Last updated: November 12, 2021 - 6:30 am UTC

Version: 19.2

Viewed 1000+ times

You Asked

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.



and Connor said...

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;
/




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.