0

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

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

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>
dcdum2018
  • 93
  • 1
  • 9
  • what's your DBMS, seems Oracle ...? – Barbaros Özhan Sep 09 '18 at 07:27
  • 1
    Possible duplicate of [Drop partitions older than 2 months](https://stackoverflow.com/questions/14933828/drop-partitions-older-than-2-months) – Thomas G Sep 09 '18 at 07:51
  • 1
    *"Table is partitioned per month"* - please confirm that this means range partitioning. – APC Sep 09 '18 at 12:18
  • For *interval partitioning* see the solution [here](https://stackoverflow.com/questions/48089186/drop-multiple-partitions-based-on-date/52171170#52171170) – Marmite Bomber Sep 10 '18 at 05:33
  • Your partition definitions is rather strange. From my understanding partition `partitionname2_P60` contains data from 30th of January to 1st of March (in case of leaf year), i.e. it covers three months - what do you mean by "older than 2 months"?. Make a simple `INTERVAL` partition on `TABLE_DATE` or on virtual column `EXTRACT(MONTH FROM TABLE_DATE)` – Wernfried Domscheit Sep 11 '18 at 09:52
  • Can you please run `select table_name, partition_name, HIGH_VALUE from all_tab_partitions where table_name='TABLENAME';` – Wernfried Domscheit Sep 11 '18 at 09:54
  • i meant, there are 12 partitions (1 partition per month), When i run query, below shows the result: table_name=`TABLENAME' partition_name='partitionname1_P30' high_value='30`. updating question again... – dcdum2018 Sep 11 '18 at 09:59
  • What happens with the last days in a year? One year has 365/366 days, not 360! – Wernfried Domscheit Sep 11 '18 at 10:03

1 Answers1

1

You can do it like this:

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;

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.

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

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

Wernfried Domscheit
  • 54,457
  • 9
  • 76
  • 110