Automation Script to maintain Oracle Table Subpartitions

Question

I am new to PLSQL.

Can someone please provide me automation script to maintain(drop and create) subpartition in the Oracle table. I know the script to maintain the table partition but unable to frame for Subpartitions.

Some details:

Range-Range partition
Subpartition: On date column (Monthly)
Retention : 180days data

Here is how Oracle table definition:

CREATE TABLE PART_TABLE  
   (    
    "REQUEST_ITEM_ID" NUMBER NOT NULL ENABLE,  
    "X_CLOB" CLOB,  
    "ENQUEUED_COUNT" NUMBER, 
    "UPDATE_LAST_META" NUMBER(1,0),  
    "CFI_TYPE_ID" NUMBER,  
   ) SEGMENT CREATION IMMEDIATE  
 LOB ("X_CLOB") STORE AS BASICFILE (TABLESPACE "DATA_TS" ENABLE  
  PARTITION BY RANGE (PRIORITY)  
  SUBPARTITION BY RANGE (CREATED)  
  (PARTITION PART_01 VALUES less THAN(2)  
   (  
    SUBPARTITION PART_01_FEB_2020 VALUES LESS THAN (TO_DATE('2020-03-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')),  
    SUBPARTITION PART_01_MAR_2020 VALUES LESS THAN (TO_DATE('2020-04-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')),  
    SUBPARTITION PART_01_MAX VALUES LESS THAN (MAXVALUE)  
    ),  
    PARTITION PART_02 VALUES less THAN(3)  
   (  
    SUBPARTITION PART_02_FEB_2020 VALUES LESS THAN (TO_DATE('2020-03-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')),  
    SUBPARTITION PART_02_MAR_2020 VALUES LESS THAN (TO_DATE('2020-04-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')),  
    SUBPARTITION PART_02_MAX VALUES LESS THAN (MAXVALUE)  
    )  
     PARTITION PART_03 VALUES less THAN(4)  
   (  
    SUBPARTITION PART_03_FEB_2020 VALUES LESS THAN (TO_DATE('2020-03-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')),
    SUBPARTITION PART_03_MAR_2020 VALUES LESS THAN (TO_DATE('2020-04-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')),
    SUBPARTITION PART_03_MAX VALUES LESS THAN (MAXVALUE)
    ));

Answer 1

I would say, your table has wrong partition pattern. It should be better like this:

CREATE TABLE ... (
   your columns
)
PARTITION BY RANGE (CREATED) INTERVAL (INTERVAL '1' MONTH)
SUBPARTITION BY RANGE (PRIORITY)
SUBPARTITION TEMPLATE
   (
   SUBPARTITION PART_01 VALUES LESS THAN(2), 
   SUBPARTITION PART_02 VALUES LESS THAN(3), 
   SUBPARTITION PART_03 VALUES LESS THAN(4),
   SUBPARTITION PART_MAX VALUES LESS THAN (MAXVALUE)
   )
( 
   PARTITION P_BEFORE_2019 VALUES LESS THAN (TIMESTAMP '2019-01-01 00:00:00') 
);

Then new partitions will be created automatically by Oracle and deletion would be easier as well.

Anyway, a procedure for maintenance could be like this (not tested):

DECLARE

    ts TIMESTAMP;
    sqlstr VARCHAR2(1000);

    CURSOR TabSubPartitions IS
    SELECT TABLE_NAME, PARTITION_NAME, SUBPARTITION_NAME, HIGH_VALUE
    FROM USER_TAB_SUBPARTITIONS
    WHERE TABLE_NAME = 'PART_TABLE'
    ORDER BY PARTITION_NAME, SUBPARTITION_NAME;

BEGIN

    FOR aSubPart IN TabSubPartitions LOOP
      IF aSubPart.HIGH_VALUE <> 'MAXVALUE' THEN
           EXECUTE IMMEDIATE 'BEGIN :ret := '||aSubPart.HIGH_VALUE||'; END;' USING OUT ts;  
            IF ts < SYSTIMESTAMP - INTERVAL '180' DAY THEN
               -- As far as I remember you cannot drop a subpartition, thus you can only truncate it
               sqlstr := 'ALTER TABLE '||aSubPart.TABLE_NAME||' TRUNCATE SUBPARTITION '||aSubPart.SUBPARTITION_NAME||' UPDATE GLOBAL INDEXES';
              EXECUTE IMMEDIATE sqlstr;
            END IF;
        ELSE
            IF TRUNC(LAST_DAY(SYSDATE)) = TRUNC(SYSDATE) THEN
                -- If last day of current months then create new monthly partition 
                -- Perhaps further checks/exception handler are needed to check whether subpartition already exist.
                sqlstr := 'ALTER TABLE '||aSubPart.TABLE_NAME||' SPLIT SUBPARTITION '||aSubPart.SUBPARTITION_NAME
                    ||' AT (TIMESTAMP '''||TO_CHAR(SYSDATE+1, 'YYYY-MM-DD HH24:MI:SS')||''') INTO ('
                    ||' SUBPARTITION '||aSubPart.PARTITION_NAME||'_'||TO_CHAR(SYSDATE+1, 'MON_YYYY')||'),'
                    ||' SUBPARTITION '||aSubPart.PARTITION_NAME||'_MAX)  UPDATE GLOBAL INDEXES';
                EXECUTE IMMEDIATE sqlstr;
            END IF;
        END IF;
    END LOOP;

END;

With a "proper" table design as given at the top the maintenance would be simpler:

DECLARE

    CANNOT_DROP_LAST_PARTITION EXCEPTION;
    PRAGMA EXCEPTION_INIT(CANNOT_DROP_LAST_PARTITION, -14758);

    ts TIMESTAMP;
    sqlstr VARCHAR2(1000);

    CURSOR TabPartitions IS
    SELECT TABLE_NAME, PARTITION_NAME, HIGH_VALUE
    FROM USER_TAB_PARTITIONS
    WHERE TABLE_NAME = 'PART_TABLE'
    ORDER BY PARTITION_NAME;

BEGIN

    FOR aPart IN TabPartitions LOOP
    BEGIN
       EXECUTE IMMEDIATE 'BEGIN :ret := '||aPart.HIGH_VALUE||'; END;' USING OUT ts; 
        IF ts < SYSTIMESTAMP - INTERVAL '180' DAY THEN
           sqlstr := 'ALTER TABLE '||aPart.TABLE_NAME||' DROP PARTITION '||aSubPart.PARTITION_NAME||' UPDATE GLOBAL INDEXES';
          EXECUTE IMMEDIATE sqlstr;
        END IF;
    EXCEPTION
        WHEN CANNOT_DROP_LAST_PARTITION THEN
            EXECUTE IMMEDIATE 'ALTER TABLE '||aPart.TABLE_NAME||' SET INTERVAL ()';
            EXECUTE IMMEDIATE 'ALTER TABLE '||aPart.TABLE_NAME||' DROP PARTITION ('||aPart.PARTITION_NAME||') UPDATE INDEXES';
            EXECUTE IMMEDIATE 'ALTER TABLE '||aPart.TABLE_NAME||' SET INTERVAL (INTERVAL ''1'' MONTH)';            
    END;
    END LOOP;
END;

You may skip the exception handler WHEN CANNOT_DROP_LAST_PARTITION THEN ..., this exception appears only once and when you get then you can run the three command manually. After that this exception will never raised again.