截断超过 2 个月的分区 [英] Truncate partitions older than 2 months

问题描述

如何截断数据超过 2 个月的分区?

How to truncate partitions with data older than 2 months?

例如，我有以下表/分区名称:

For an example, i have below table/partition names:

select table_name, partition_name from all_tab_partitions where table_name='TABLENAME';

TABLENAME   partitionname1_P30    30
TABLENAME   partitionname2_P60    60 
TABLENAME   partitionname3_P90    90
TABLENAME   partitionname4_P120   120
TABLENAME   partitionname5_P150   150
TABLENAME   partitionname6_P180   180 
TABLENAME   partitionname7_210    210
TABLENAME   partitionname8_P240   240
TABLENAME   partitionname9_P270   270
TABLENAME   partitionname10_P300  300
TABLENAME   partitionname11_P330  330
TABLENAME   partitionname12_P360  360  

表按月分区.如果我们目前是 9 月，我该如何截断 2 个月以上的分区?

Table is partitioned per month. If we're currently on September, how do I truncate partitions older than 2 months?

预计只有 8 月至 9 月(分区名称 8-9)的记录会保留，而其余记录将被截断.

Expectation is that only records from Aug-Sep (partitionname8-9) will remain while the rest will be truncated.

CREATE TABLE dbo1.TABLENAME
( PARTITION_ID NUMBER(4, 0) NOT NULL, 
TABLE_DATE DATE NOT NULL, 
TABLE_TIMESTAMP NUMBER(19, 0) NOT NULL, 
TABLE_BUNDLE_ID VARCHAR2(240 BYTE) NOT NULL, 
TABLE_TYPE NUMBER(8, 0) NOT NULL, 
TABLE_SEVERITY NUMBER(19, 0) NOT NULL,
TABLE_FACILITY NUMBER(19, 0) NOT NULL,
TABLE_HOST VARCHAR2(120 BYTE) NOT NULL,
TABLE_PROCESS VARCHAR2(240 BYTE) NOT NULL,
TABLE_SYSTEM VARCHAR2(240 BYTE) NOT NULL,
TABLE_SESSION_ID VARCHAR2(240 BYTE) NOT NULL,
TABLE_PRINCIPAL VARCHAR2(120 BYTE) NOT NULL,
OBJECT_ID VARCHAR2(120 BYTE),
OBJECT_TYPE VARCHAR2(2 BYTE),
CLIENT_HOST VARCHAR2(120 BYTE),
ACCESS_HOST VARCHAR2(120 BYTE),
SCOPE_ID VARCHAR2(120 BYTE),
STATUS NUMBER(19, 0),
OBJECT_HISTORY NUMBER(19, 0),
TABLE_DETAILS VARCHAR2(4000 BYTE) 
) 
PARTITION BY RANGE (PARTITION_ID) 
(
PARTITION partitionname1_P30 VALUES LESS THAN (30) 
,<repeat partition by 30s up to 360, total of 12 partitions>

推荐答案

你可以这样做:

DECLARE

    CURSOR PartTables IS
    SELECT TABLE_NAME, PARTITION_NAME, HIGH_VALUE
    FROM USER_TAB_PARTITIONS
    WHERE TABLE_NAME = 'TABLENAME';

    highValue TIMESTAMP;

BEGIN
   FOR aTab IN PartTables LOOP
      EXECUTE IMMEDIATE 'BEGIN :ret := '||aTab.HIGH_VALUE||'; END;' USING OUT highValue;
      IF highValue < ADD_MONTHS(SYSDATE, -2) THEN
         EXECUTE IMMEDIATE 'ALTER TABLE TABLENAME TRUNCATE PARTITION '||aTab.PARTITION_NAME||' UPDATE INDEXES';
      END IF;
   END LOOP;
END;

这适用于基于 RANGE 或 INTERVAL 的分区，但是在这种情况下，您的要求相当无用，因为您将永远保留空分区.通常您会删除旧分区，为此只需将 TRUNCATE 替换为 DROP.

This would work for a RANGE or INTERVAL based partitions, however in this case your requirement is rather useless because you would keep empty partitions for ever. Usually you drop old partitions, for that just replace TRUNCATE by DROP.

如果您的分区基于 LIST，即月数，则解决方案是:

In case your partition is base on LIST, i.e. month number the solution would be this:

DECLARE

    CURSOR PartTables IS
    SELECT TABLE_NAME, PARTITION_NAME, HIGH_VALUE
    FROM USER_TAB_PARTITIONS
    WHERE TABLE_NAME = 'TABLENAME';

    highValue INTEGER;

BEGIN
   FOR aTab IN PartTables LOOP
      EXECUTE IMMEDIATE 'BEGIN :ret := '||aTab.HIGH_VALUE||'; END;' USING OUT highValue;
      IF highValue NOT IN (
            EXTRACT(MONTH FROM SYSDATE),
            EXTRACT(MONTH FROM ADD_MONTHS(SYSDATE, -1))
         ) 
      THEN
         EXECUTE IMMEDIATE 'ALTER TABLE TABLENAME TRUNCATE PARTITION '||aTab.PARTITION_NAME||' UPDATE INDEXES';
      END IF;
   END LOOP;
END;

根据你奇怪的分区定义，条件是

Based on your strange partition definition, the conditions would be

IF highValue NOT IN (
    30*CEIL(TO_CHAR(SYSDATE, 'fmddd')/30), 
    30*CEIL(TO_CHAR(ADD_MONTHS(SYSDATE, -1), 'fmddd')/30)
    ) 
THEN

但如果您在 12 月 26 日至 31 日之间运行该程序，则可能会遇到问题.

but you may run into issues if you run the procedure between 26th - 31st of December.

这篇关于截断超过 2 个月的分区的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！