Delete huge data - truncate partition programmatically

Question

As described in this question, I have the follow table:

create table prova_log(
       id_dispositive       number,
       type                 number,
       date_verification    date,
       status               number
)
partition by range (date_verification) interval (numtoyminterval(1,'MONTH'))
subpartition by list (type)  
subpartition TEMPLATE (
    SUBPARTITION type1 VALUES (1),
    SUBPARTITION type2 VALUES (2),
    SUBPARTITION type3 VALUES (3),
    SUBPARTITION type4 VALUES (4)
)                          
(
   partition p0816 values less than (to_date('01/09/2016','dd/mm/yyyy'))
);

As you can see, I named the first partition as p0816. But the next partitions will be generated with a random name right?

I need to purge the table programmatically within an oracle job. I was thinking - as my scenario is to deal with 1B rows/month - to drop a partition. But how can I do that? How to find out a table partition by date ranges?

I want to do something like:

alter table prova_log drop partition XPTO

"XPTO" would be a random partition name, but selected by date range - lets say 3 months before. In other words, delete everything before 2016/08/01

Answer 1

In Oracle you have two ways to address a partition, they are equivalent.

ALTER TABLE prova_log DROP PARTITION p0816;

ALTER TABLE prova_log DROP PARTITION FOR ( to_date('01/09/2016','dd/mm/yyyy') );

If you like to address a SUBPARTITION the syntax would be like this:

ALTER TABLE prova_log TRUNCATE SUBPARTITION FOR ( to_date('01/09/2016','dd/mm/yyyy'), 2 );

In order to drop (or truncate) partitions, I have this generic PL/SQL package. The most tricky part is to convert HIGH_VALUE which is a LONG data type into a usable value.

FUNCTION DailyPartition(tableName IN VARCHAR2) RETURN BOOLEAN IS    
    EXPRESSION_IS_OF_WRONG_TYPE EXCEPTION;
    PRAGMA EXCEPTION_INIT(EXPRESSION_IS_OF_WRONG_TYPE, -6550);    

    ds INTERVAL DAY TO SECOND;
    ym INTERVAL YEAR TO MONTH;
    str VARCHAR2(1000);     

BEGIN    
    SELECT INTERVAL INTO str FROM USER_PART_TABLES  WHERE TABLE_NAME = tableName;    
    EXECUTE IMMEDIATE 'BEGIN :ret := '||str||'; END;' USING OUT ym;
    RETURN FALSE;    
EXCEPTION 
    WHEN EXPRESSION_IS_OF_WRONG_TYPE THEN
        EXECUTE IMMEDIATE 'BEGIN :ret := '||str||'; END;' USING OUT ds;
        RETURN TRUE;    
END DailyPartition;


PROCEDURE DropPartition(tableName IN VARCHAR2, ts IN TIMESTAMP) IS    
    PARTITION_DOES_NOT_EXIST EXCEPTION;
    PRAGMA EXCEPTION_INIT(PARTITION_DOES_NOT_EXIST, -2149);
    sqlstr VARCHAR2(1000);    

BEGIN    
    sqlstr := 'ALTER TABLE '||tableName||' DROP PARTITION FOR (TIMESTAMP '''||TO_CHAR(ts, 'yyyy-mm-dd hh24:mi:ss')||''') UPDATE GLOBAL INDEXES';
    EXECUTE IMMEDIATE sqlstr;    
EXCEPTION 
    WHEN PARTITION_DOES_NOT_EXIST THEN
        NULL;
END DropPartition;



PROCEDURE CleanupPartitions IS
    sqlstr VARCHAR2(10000);
    ts TIMESTAMP;
    tableName VARCHAR2(30) := 'PROVA_LOG';

    CURSOR TabPartitions IS
    SELECT TABLE_NAME, PARTITION_NAME, HIGH_VALUE
    FROM USER_TAB_PARTITIONS 
    WHERE TABLE_NAME = tableName
        AND PARTITION_NAME <> 'P0816' -- Inital partition cannot be dropped
    ORDER BY 1,2;

BEGIN

    ts := ADD_MONTHS(TRUNC(LOCALTIMESTAMP) - INTERVAL '1' DAY, -6);

    IF DailyPartition(tableName) THEN
        DropPartition(tableName, ts);
        IF EXTRACT(DAY FROM ts) >= 30 THEN
            DropPartition(tab, ts - INTERVAL '1' DAY);
        END IF;
        IF TO_CHAR(ts, 'MM-DD') = '08-31' THEN 
            -- Ensure proper cleanup for February 
            DropPartition(tableName, ts - INTERVAL '2' DAY);
            DropPartition(tableName, ts - INTERVAL '3' DAY);
        END IF;
    ELSE
        DropPartition(tableName, ts);
    END IF;


END CleanupPartitions;

Retention time is 6 months. It should be not problem to make it according to your needs. You can call procedure CleanupPartitions; even several times. Typically it will be executed by a Scheduler Job once a month or day.

The package works for daily and monthly partitions.

Answer 2

No need to use random names, use a predictable partition name based on date.

Example from docs, naming the partitions (Maintaining Partitions ):

ALTER TABLE sales ADD 
  PARTITION sales_q1_2007 VALUES LESS THAN (TO_DATE('01-APR-2007','dd-MON-yyyy')),
  PARTITION sales_q2_2007 VALUES LESS THAN (TO_DATE('01-JUL-2007','dd-MON-yyyy')),
  PARTITION sales_q3_2007 VALUES LESS THAN (TO_DATE('01-OCT-2007','dd-MON-yyyy')),
  PARTITION sales_q4_2007 VALUES LESS THAN (TO_DATE('01-JAN-2008','dd-MON-yyyy'))
;

Also, you don't necessarily need to know the name up front if you always drop the tail partition. You can query the tail partition from the data dictionary (ALL_TAB_PARTITIONS, ALL_PART_TABLES, etc.)

I would use a naming scheme that is easy to remember and code to. Use the month and year, and if you need more granularity, the week or some sequential number within the month, then again, a query against the dictionary with a LIKE clause should give you what you need.

Answer 3

I use script smt like this(Change table name to yours)

declare
  l_limit_date date := sysdate;
begin
  for c in (select table_name,
                   partition_name,
                   interval,
                   high_value_in_date_format
              from (select table_name,
                           partition_name,
                           interval,
                           to_date(trim('''' from regexp_substr(extractvalue(dbms_xmlgen.
                                                                   getxmltype('select high_value from user_tab_partitions where table_name=''' ||
                                                                              table_name ||
                                                                              ''' and partition_name = ''' ||
                                                                              partition_name || ''''),
                                                                   '//text()'),
                                                      '''.*?''')),
                                   'syyyy-mm-dd hh24:mi:ss') high_value_in_date_format
                      from user_tab_partitions
                     where table_name = 'TEST_LOG')
             where high_value_in_date_format <= l_limit_date
             order by high_value_in_date_format) 
  loop
    if (c.interval = 'NO') then
      execute immediate 'alter table TEST_LOG truncate partition ' || c.partition_name;
    else
      execute immediate 'alter table TEST_LOG drop partition ' ||  c.partition_name;
    end if;
  end loop;
end;
/