在Oracle 11G中自动删除最旧的分区 [英] Drop oldest partition automatically in oracle 11G

问题描述

如果分区的时间超过三个月，我需要从间隔分区表中删除分区.

I have a requirement to drop partition from an interval partitioned table, if the partition is older than three months.

是否有oracle实用程序/函数可以执行此操作?否则，如何实施呢?请引导我.

Is there a oracle utility/function to do this? Or if not, how to implement this? Please guide me.

Database version: Oracle 11G

推荐答案

我不知道有任何Oracle实用程序或函数可以执行此操作.您可以在DBA_TAB_PARTITIONS或ALL_TAB_PARTITIONS视图中找到编写自己的程序所需的信息，类似于以下内容:

I don't know of any oracle utility or function to do this. You can find the information you need to write your own program to do this in the DBA_TAB_PARTITIONS or ALL_TAB_PARTITIONS views, similar to the following:

SELECT TABLE_OWNER, TABLE_NAME, PARTITION_NAME, HIGH_VALUE
  FROM SYS.DBA_TAB_PARTITIONS
  WHERE TABLE_OWNER = strSchema AND
        TABLE_NAME = strTable

其中strSchema和strTable是您感兴趣的模式和表.HIGH_VALUE是LONG字段，其中包含调用TO_DATE函数的代码(假定您的表已在date字段上分区)；您需要将HIGH_VALUE分配给LONG字段，然后将LONG分配给VARCHAR2以便以类似于以下方式的方式获得可以操纵的值:

where strSchema and strTable are the schema and table you're interested in. HIGH_VALUE is a LONG field which contains the code for a call to the TO_DATE function (assuming your table is partitioned on a date field); you'll need to assign HIGH_VALUE to a LONG field, then assign the LONG to a VARCHAR2 in order to get the value somewhere it can be manipulated, in a manner similar to:

lHigh_value     LONG;
strDate_clause  VARCHAR2(100);

lHigh_value := aRow.HIGH_VALUE;
strDate_clause := lHigh_value;

然后，您只需要从DATE子句中提取适当的字段，即可确定需要删除的分区.

Then you just need to extract the appropriate fields from the DATE clause in order to determine which partitions you need to drop.

分享并享受.

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