Oracle:删除多个分区 [英] Oracle : Drop multiple partitions

问题描述

表 TMP 具有5个分区，即P_1，P_2，.... P_5. 我需要删除 TMP 的一些分区；要删除的分区是由另一个查询派生的.

A table TMP has 5 partitions, namely P_1, P_2,....P_5. I need to drop some partitions of TMP; the partitions to drop are derived by another query.

例如:

ALTER TABLE TMP DROP PARTITIONS (SELECT ... From ...  //expression to get partition names )

假设SELECT语句返回P_1& P_5.上面的ALTER语句的部分查询不起作用.有什么方法可以使用SELECT语句的输入删除分区?

Let's say the SELECT statement returns P_1 & P_5. The part query of the ALTER statement above doesn't work. Is there any way to drop partitions with input from a SELECT statement?

推荐答案

您可以在anonymous pl/sql块中使用dynamic sql；

Begin
  for i in (select part_name from ...  //expression to get partition names) loop
    execute immediate 'ALTER TABLE TMP DROP PARTITION ' || i.part_name;
  end loop;
end;

然后要删除多个分区；

For dropping multiple partitions on a go then;

declare
  v_part varchar(1000);
Begin
  select LISTAGG(partition_name, ', ') WITHIN GROUP (ORDER BY partition_name DESC)
  into v_part  
  from ...  //expression to get partition names;
  execute immediate 'ALTER TABLE TMP DROP PARTITION ' || v_part;
end;

这篇关于Oracle:删除多个分区的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！