REM   Script: 18c merge online 

REM   Merge partitions on line without service interruption 

drop table sales purge

create table sales  
  ( ts   date,  
    id   int,  
    amt  number,  
    completed varchar2(1)  
  )  
partition by range ( ts )   
interval ( numtodsinterval(1,'DAY') )  
(   
  partition p1 values less than ( date '2017-01-01' ),  
  partition p2 values less than ( date '2017-02-01' ),  
  partition p3 values less than ( date '2017-03-01' ),  
  partition p4 values less than ( date '2017-04-01' ),  
  partition p5 values less than ( date '2017-05-01' )  
) 

insert /*+ APPEND */ into sales  
select date '2017-01-01' - 10 + rownum / 100 ,rownum, dbms_random.value(1,100), null  
from ( select 1 from dual connect by level <= 100 ),  
     ( select 1 from dual connect by level <= 120 ) 

exec dbms_stats.gather_table_stats('','SALES') 

select partition_name, num_rows  
from user_tab_partitions  
where table_name = 'SALES'  
order by partition_position 

create index sales_ix on sales ( id ) 

alter table sales merge partitions p3, p4 into partition p3a update indexes online