截断超过2个月的分区

Question

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

举个例子，我有下面的表/分区名：

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个月的分区？

预期只有8-9月(分区名8-9)的记录将保留，而其余记录将被截断。

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>

Answer 1

你可以这样做：

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;

这将适用于基于范围或间隔的分区，但是在这种情况下，您的需求相当无用，因为您将永远保留空分区。通常您会删除旧分区，因此只需将TRUNCATE替换为DROP即可。

如果您的分区基于列表，即月份编号，则解决方案如下：

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;

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

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号之间运行这个程序，你可能会遇到问题。