Need a PL/SQL code to drop many partition in 1 single SQL statements

Question

I have a requirement where we need to drop historical partitions which are not dropped . Requirement is dropping multiple partitions in one step instead of dropping one by one.

like

ALTER TABLE sales DROP PARTITION sales_q1_2008, sales_q2_2008,
sales_q3_2008, sales_q4_2008 update global indexes;

instead of

ALTER TABLE sales DROP PARTITION sales_q1_2008 update global indexes;
ALTER TABLE sales DROP PARTITION sales_q2_2008 update global indexes;
ALTER TABLE sales DROP PARTITION sales_q3_2008 update global indexes;

and so on...

app team has this code:-

CREATE OR REPLACE PROCEDURE Test_user.DROP_PARTITIONS (p_table_name in varchar2, p_partition_key in number)
is
  pragma autonomous_transaction;
begin
  for cur in (
    select partition_name from user_tab_partitions
      where table_name = p_table_name
      and substr(partition_name,1,8) <= 'P_'||trim(to_char(p_partition_key,'000000'))
      order by partition_position desc
  ) loop
    -- wait for processes which might have a lock on the partition to drop (max 10min)
    execute immediate 'lock table '||p_table_name||' partition ('||cur.partition_name||') in exclusive mode wait 600';
 
    execute immediate 'alter table '||p_table_name||' drop partition '||cur.partition_name || ' UPDATE GLOBAL INDEXES';
  end loop;
end;
/

But I guess this is doing serial drop for each partitions thereby creating many alter statements instead of 1 step command where all the necessary partitions are dropped.

How can we adapt this ?

Answer 1

Something like this:

create or replace procedure test_user.drop_partitions (p_table_name in varchar2, p_partition_key in number)
is
  l_partition_list varchar2(32767);
begin
  select listagg(partition_name, ',') within group (order by partition_position) into l_partition_list
  from user_tab_partitions
  where table_name = p_table_name
  and substr(partition_name,1,8) <= 'P_'||trim(to_char(p_partition_key,'000000'));
  
  execute immediate 'alter session set ddl_lock_timeout=600';
  execute immediate 'alter table '||p_table_name||' drop partition '|| l_partition_list || ' UPDATE GLOBAL INDEXES';
end;
/