create or replace
procedure merge_old_partitions(p_owner varchar2, 
                           p_table_name varchar2, 
                           p_threshold date, 
                           p_parsize_days_no_merge_needed pls_integer default 35,
                           p_merge_performed out boolean,
                           p_defer_index_rebuild boolean default false) is
  l_parpos1 pls_integer;
  l_parpos2 pls_integer;

  l_pname1  varchar2(2000);
  l_pname2  varchar2(2000);
  l_hival1  varchar2(2000);
  l_hival2  varchar2(2000);
  l_interval varchar2(1000);
  l_days     pls_integer;
  l_dte     date;

  procedure logger(p_msg varchar2, p_table_name varchar2) is
  begin
    dbms_output.put_line(p_table_name||':'||p_msg);
  end;

  procedure ddl(p_ddl varchar2) is
  begin
    logger(p_msg=>p_ddl, p_table_name=>p_table_name);
    execute immediate p_ddl;
  end;

  procedure check_all_indexes is
  begin
    for i in ( 
        select index_owner, index_name, partition_name, 'partition' ddl_type
        from all_ind_partitions
        where (index_owner,index_name) in 
           ( select owner, index_name
             from   all_indexes
             where table_owner = upper(p_owner)
             and   table_name  = upper(p_table_name)
           )
        and status = 'UNUSABLE'
        union all
        select index_owner, index_name, subpartition_name, 'subpartition' ddl_type
        from all_ind_subpartitions
        where (index_owner,index_name) in 
           ( select owner, index_name
             from   all_indexes
             where table_owner = upper(p_owner)
             and   table_name  = upper(p_table_name)
           )
        and status = 'UNUSABLE'
        union all
        select owner, index_name, null, null
        from all_indexes
        where table_owner = upper(p_owner)
        and   table_name  = upper(p_table_name)
        and status = 'UNUSABLE'
    )
    loop
      if i.ddl_type is null then
        ddl('alter index '||i.index_owner||'.'||i.index_name||' rebuild');
      else
        ddl('alter index '||i.index_owner||'.'||i.index_name||' rebuild '||i.ddl_type||' '||i.partition_name);
      end if;
    end loop;
  end;
begin
  logger(p_msg=>'Checking for partition merges, threshold='||p_threshold, p_table_name=>p_table_name);

  --
  -- we walk forward from the lowest partition until we find a partition gap
  -- that is less than 'p_parsize_days_no_merge_needed', avoid we dont end up
  -- with a gigantic partitions
  --
  -- So if I have partitions
  --    p1
  --    p2
  --    p3
  -- then I can merge p3 and p2, if the resulting p3 to p1 is less than 'p_parsize_days_no_merge_needed'
  -- so I need to track 3 consecuctive partitions (ie, to assess 2 ranges between them)
  --
  for i in ( 
     select *
     from (
       select partition_name,
              partition_position, 
              high_value,
              lag(partition_name,1) over ( order by partition_position) as parname_minus_1, 
              lag(partition_position,2) over ( order by partition_position) as parpos_minus_2
       from   all_tab_partitions
       where  table_owner = upper(p_owner)
       and    table_name  = upper(p_table_name)
     )
     where parpos_minus_2 is not null
     order by partition_position )
  loop
     l_hival2 := i.high_value;
     --
     -- can't do lag on a long :-(
     --
     select high_value
     into   l_hival1 
     from   all_tab_partitions
     where  table_owner = upper(p_owner)
     and    table_name  = upper(p_table_name)
     and    partition_position = i.parpos_minus_2;

     if l_hival1 like 'TIMESTAMP%' then
       execute immediate 'select  extract(day from '||l_hival2||' - '||l_hival1||') from dual' into l_days;
     elsif l_hival1 like '%DATE%' then
       execute immediate 'select '||l_hival2||' - '||l_hival1||' from dual' into l_days;
     else
        exit;
     end if;
     
     if l_days < p_parsize_days_no_merge_needed then
        l_pname1 := i.parname_minus_1;
        l_pname2 := i.partition_name;
        exit;
     else
        logger(p_msg=>'- '||i.parname_minus_1||' not considered, resulting width='||l_days, p_table_name=>p_table_name);
     end if;
  end loop;
   
  if l_pname1 is null or l_pname2 is null then
    p_merge_performed := false;
    return;
  end if;

  logger(p_msg=>'- partition 1 is '||l_pname1||','||l_hival1, p_table_name=>p_table_name);
  logger(p_msg=>'- partition 2 is '||l_pname2||','||l_hival2, p_table_name=>p_table_name);
  
  execute immediate 'select '||l_hival1||' from dual' into l_dte;

  if l_dte < p_threshold then
    select interval
    into   l_interval
    from   all_part_tables
    where  owner = upper(p_owner)
    and    table_name = upper(p_table_name);
    
    if l_interval is not null then
      ddl('alter table '||p_owner||'.'||p_table_name||' set interval ( '||l_interval||')');
    end if;
    
    ddl('alter table '||p_owner||'.'||p_table_name||' merge partitions '||l_pname1||','||l_pname2||' into partition '||l_pname2||' compress');

    if p_defer_index_rebuild then 
      logger('- deferring index rebuild', p_table_name=>p_table_name);
    else
      ddl('alter table '||p_owner||'.'||p_table_name||' modify partition '||l_pname2||' rebuild unusable local indexes');
      check_all_indexes;
    end if;

    p_merge_performed := true;

  else
    p_merge_performed := false;
    
    --
    -- if we deferred index maint until no more merges done
    -- now is the time to rebuild them all
    --
    if p_defer_index_rebuild then 
      logger('Performing index rebuild check after all merge work completed', p_table_name=>p_table_name);
      check_all_indexes;
    end if;
  end if;
  
exception
  when no_data_found then
     -- could not find 2 partitions, so no work required
         p_merge_performed := false;
end;
/

set serverout on
declare
  l_more_work boolean;
begin
  loop
    merge_old_partitions(
           p_owner=>user,
           p_table_name=>'ABC',
           p_threshold=>add_months(sysdate,-1),
           p_parsize_days_no_merge_needed=>14,
           p_merge_performed=>l_more_work);
    exit when not l_more_work;
  end loop;
end;
/