Exchange partition example

test-case-out.sql

SQL> create table t
  2    partition by range(RYEAR,RMONTH)
  3      (
  4       partition p1 values less than (2021,1)
  5      ,partition p2 values less than (2022,1)
  6      ,partition p3 values less than (2023,1)
  7      ,partition p4 values less than (2024,1)
  8      ,partition pm values less than (maxvalue,maxvalue)
  9      )
 10  as
 11  with
 12   ry(RYEAR) as (select * from table(sys.odcinumberlist(2020,2021,2022,2023)))
 13  ,rm(RMONTH) as (select * from xmltable('1 to 12' columns n int path '.'))
 14  ,gen(n) as (select * from xmltable('1 to 10' columns n int path '.'))
 15  select
 16      RYEAR,RMONTH
 17     ,n
 18     ,case when mod(n,3)=0 then 1 else 0 end as PR_DEL
 19  from ry,rm,gen;

Table created.

SQL>
SQL> select PR_DEL,count(*) from t group by PR_DEL;

    PR_DEL   COUNT(*)
---------- ----------
         1        144
         0        336

2 rows selected.

SQL>
SQL> create table tpart as select * from t where 1=0;

Table created.

SQL>
SQL> declare
  2    procedure p_exec(cmd in varchar2) as
  3    begin
  4      dbms_output.put_line(cmd);
  5      execute immediate cmd;
  6    end;
  7  begin
  8    for part in (select p.partition_name from user_tab_partitions p where p.table_name='T' order by p.partition_position)
  9    loop
 10      p_exec('truncate table tpart');
 11      p_exec(
 12         utl_lms.format_message(
 13             'insert/*+ append */ into tpart select * from t partition (%s) where PR_DEL<>1'
 14            ,part.partition_name
 15         )
 16      );
 17      commit;
 18      p_exec(
 19         utl_lms.format_message(
 20             'alter table t
 21                 exchange partition %s
 22                 with table tpart
 23                 without validation
 24                 update global indexes'
 25             ,part.partition_name
 26         )
 27      );
 28      --p_exec('');
 29    end loop;
 30  end;
 31  /

PL/SQL procedure successfully completed.

SQL> ;
  1  declare
  2    procedure p_exec(cmd in varchar2) as
  3    begin
  4      dbms_output.put_line(cmd);
  5      execute immediate cmd;
  6    end;
  7  begin
  8    for part in (select p.partition_name from user_tab_partitions p where p.table_name='T' order by p.partition_position)
  9    loop
 10      p_exec('truncate table tpart');
 11      p_exec(
 12         utl_lms.format_message(
 13             'insert/*+ append */ into tpart select * from t partition (%s) where PR_DEL<>1'
 14            ,part.partition_name
 15         )
 16      );
 17      commit;
 18      p_exec(
 19         utl_lms.format_message(
 20             'alter table t
 21                 exchange partition %s
 22                 with table tpart
 23                 without validation
 24                 update global indexes'
 25             ,part.partition_name
 26         )
 27      );
 28      --p_exec('');
 29    end loop;
 30* end;
SQL>
SQL> select PR_DEL,count(*) from t group by PR_DEL;

    PR_DEL   COUNT(*)
---------- ----------
         0        336

1 row selected.

test.sql

--drop table t purge;
--drop table tpart purge;

create table t
  partition by range(RYEAR,RMONTH)
    (
     partition p1 values less than (2021,1)
    ,partition p2 values less than (2022,1)
    ,partition p3 values less than (2023,1)
    ,partition p4 values less than (2024,1)
    ,partition pm values less than (maxvalue,maxvalue)
    )
as
with
 ry(RYEAR) as (select * from table(sys.odcinumberlist(2020,2021,2022,2023)))
,rm(RMONTH) as (select * from xmltable('1 to 12' columns n int path '.'))
,gen(n) as (select * from xmltable('1 to 10' columns n int path '.'))
select
    RYEAR,RMONTH
   ,n
   ,case when mod(n,3)=0 then 1 else 0 end as PR_DEL
from ry,rm,gen;

select PR_DEL,count(*) from t group by PR_DEL;

create table tpart as select * from t where 1=0;

declare
  procedure p_exec(cmd in varchar2) as
  begin
    dbms_output.put_line(cmd);
    execute immediate cmd;
  end;
begin
  for part in (select p.partition_name from user_tab_partitions p where p.table_name='T' order by p.partition_position)
  loop
    p_exec('truncate table tpart');
    p_exec( 
       utl_lms.format_message(
           'insert/*+ append */ into tpart select * from t partition (%s) where PR_DEL<>1'
          ,part.partition_name
       )
    );
    commit;
    p_exec(
       utl_lms.format_message(
           'alter table t
               exchange partition %s 
               with table tpart 
               without validation
               update global indexes'
           ,part.partition_name
       )
    );
    --p_exec('');
  end loop;
end;
/
;

select PR_DEL,count(*) from t group by PR_DEL;