0

I have created an interval partitioned table with interval ( NUMTODSINTERVAL(1,'day') ).

As transaction data gets logged to this table on a daily basis, oracle automatically creates daily partitions.

There is a requirement to truncate the previous day's partition on a daily basis without affecting current day partition.

Since delete is producing fragmentation, I've been asked to go with truncate or drop.

mustaccio
  • 18,234
  • 16
  • 48
  • 57
NAVEEN SAKTHI
  • 1
  • 2

1 Answers1

1

Welcome to SO!

Please see below a basic example of truncating/dropping preceding partitions from an interval partitioned table, based on a date that you pass.
Note: You may need to fine tune this further to accommodate the various scenarios and edge cases based on specific data loading behaviors and requirements.

For illustration, I'm creating a sample table with interval partitioning as follows ...

create table testpart
(  value_date date  )
partition by range ( value_date )
interval ( NUMTODSINTERVAL(1,'day') )
( partition p1 values less than ( date '2020-01-01' ));

Checking default partition on the newly created table ..

select table_name, partition_name, interval from dba_tab_partitions where table_name = 'TESTPART';

|TABLE_NAME  |PARTITION_NAME    |INTERVAL |
|TESTPART    |P1                |NO       |

Inserting data current date (e.g. 16th April) and previous date (15th April) ..

insert into testpart select date '2020-04-15' from dual;
insert into testpart select date '2020-04-16' from dual;
commit;

Checking inserted data ..

select * from testpart;

| VALUE_DATE |
| 4/15/2020  |
| 4/16/2020  |

Checking the automatically created interval partitions along with the high_value converted to date format (explanations mentioned alongside in comments) ...
Note: I've adapted the logic from this post on SO

select 
table_name,
partition_name,
interval,
to_date ( -- convert to date datatype
          trim ( -- trim whitespaces
          '''' from regexp_substr ( 
                     extractvalue ( -- return scalar value of the single text node viz., high_value
                       dbms_xmlgen.getxmltype ( -- converts the results of a SQL query into XML format
                       'select high_value from all_tab_partitions where table_name='''
                                || table_name
                                || ''' and partition_name = '''
                                || partition_name
                                || ''''),
                             '//text()'),
                          '''.*?''')), -- regex pattern matching to fetch value between the first set of quotes
          'syyyy-mm-dd hh24:mi:ss')
          high_value_in_date_format
  FROM all_tab_partitions
 WHERE table_name = 'TESTPART' 
;

|TABLE_NAME |PARTITION_NAME |INTERVAL |HIGH_VALUE_IN_DATE_FORMAT    |
|TESTPART   |SYS_P9064429   |YES      |4/17/2020                    |
|TESTPART   |SYS_P9064428   |YES      |4/16/2020                    |
|TESTPART   |P1             |NO       |1/1/2020                     |

Creating a procedure to accept date and match that to high_value to identify immediately preceding partition in the specified table and truncating/dropping that partition ...

  create or replace procedure int_part_housekeeping
      ( p_date        date,
        p_table_name  varchar2
      )
  as
      l_part_name     varchar2(30);
  begin

      -- identifying partition based on high value in all_tab_partitions 
    select partition_name
      into l_part_name
      from all_tab_partitions
      where table_name = p_table_name
      and  
         to_date ( -- convert to date datatype
            trim ( -- trim whitespaces
            '''' from regexp_substr ( 
                       extractvalue ( -- return scalar value of the single text node
                         dbms_xmlgen.getxmltype ( -- converts the results of a SQL query into XML format
                         'select high_value from all_tab_partitions where table_name='''
                                  || table_name
                                  || ''' and partition_name = '''
                                  || partition_name
                                  || ''''),
                               '//text()'),
                            '''.*?''')), -- regex pattern matching to fetch value between the first set of quotes
            'syyyy-mm-dd hh24:mi:ss') = p_date
      ;

      -- truncating preceding partition
      dbms_output.put_line('Trucating partition for preceding interval partition :' || l_part_name );    
      execute immediate 'ALTER TABLE ' || p_table_name || ' TRUNCATE PARTITION (' || l_part_name || ')';

      -- dropping preceding partition (note: interval needs to be reset before and after the drop operation)
      dbms_output.put_line('Dropping partition for preceding interval partition :' || l_part_name); 
      execute immediate 'ALTER TABLE ' || p_table_name || ' SET INTERVAL ()';
      execute immediate 'ALTER TABLE ' || p_table_name || ' DROP PARTITION (' || l_part_name || ')';
      execute immediate 'ALTER TABLE ' || p_table_name || ' SET INTERVAL ( NUMTODSINTERVAL(1,''day'') )';

  exception 
      when others then
      dbms_output.put_line(sqlerrm);
      raise;
  end;

Execute the procedure passing current date and table name ..

set serveroutput on;
begin
    int_part_housekeeping(date'2020-04-16','TESTPART');
end;

Output:
Trucating partition for preceding interval partition :SYS_P9064428
Dropping partition for preceding interval partition :SYS_P9064428
 PL/SQL procedure successfully completed.

Checking if the required partitions were dropped ...

|TABLE_NAME |PARTITION_NAME |INTERVAL |HIGH_VALUE_IN_DATE_FORMAT    |
|TESTPART   |SYS_P9064429   |NO       |4/17/2020                    |
|TESTPART   |P1             |NO       |1/1/2020                     |

You may then execute this procedure on-demand or use dbms scheduler to run at specific times.

If this suits your requirement, feel free to accept and vote

vishnudattan
  • 476
  • 3
  • 10