Kategorie: Partitions

Hello fellow DBAs,

it’s time to share another tidbit from my daily work.

Given, that we have a table in our schema, that has a range partiton by month, our application people wanted to have a procedure to move all partitions older than a month to an archive tablespace and all older than 90 Days to be deleted, I came up with this small bit of code to make their lives easier.

First, let’s take a look at how the table is created.

CREATE TABLE DB_USER.PART_TABLE_1 
   (	
   ID01 NUMBER(19,0) NOT NULL ENABLE, 
   DATA1 varchar2(4000), 
   DATA2 varchar2(4000), 
   CREATE_TIMESTAMP TIMESTAMP (6) NOT NULL ENABLE 
   ) TABLESPACE DATA 
   PARTITION BY RANGE (CREATE_TIMESTAMP) INTERVAL (NUMTOYMINTERVAL(1, 'MONTH')) 
   (
     PARTITION OLD_DATA VALUES LESS THAN (TIMESTAMP' 2021-03-01 00:00:00') SEGMENT CREATION IMMEDIATE TABLESPACE DATA
   ); 
ALTER TABLE DB_USER.PART_TABLE_1 ADD UNIQUE (ID01)
  TABLESPACE DATA  ENABLE;

As you can see, I partitioned, I created a unique Index which will give me an Index on the table.
Then we fill and fill and fill the Table with lots and lots of data, partitions will be created.

And now to the interesting part, the procedures.

First, let’s move all partitons that are older than one month to the Archive Tablespace.

create or replace procedure DB_USER.MOVE_PART_TABLE_1_PARTITIONS AS
begin
for i in
(
select table_name, partition_name,high_value_in_date_format from (
   select 
          table_name,
          partition_name,
          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 = 'PART_TABLE_1') AND TABLESPACE_NAME = 'DATA'
 )
WHERE high_value_in_date_format < trunc(sysdate, 'MONTH')
)
LOOP
    execute immediate 'alter table '||i.table_name||' move partition '||i.partition_name||' tablespace ARCHIVE online update indexes';
end loop;

end;
/

The ONLINE Keyword in the move command works only on Enterprise Editions, keep that in mind.
After we moved everything, we want to get rid of old data regularly, so let’s delete old partitions.

create or replace procedure DB_USER.DELETE_PART_TABLE_1_PARTITIONS AS
begin
for i in
(
select table_name, partition_name,high_value_in_date_format from (
   select 
          table_name,
          partition_name,
          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 = 'PART_TABLE_1')  AND TABLESPACE_NAME = 'ARCHIVE'
 )
WHERE high_value_in_date_format < trunc(sysdate-90, 'MONTH')
)
LOOP
    execute immediate 'alter table '||i.table_name||' drop partition '||i.partition_name||' online update indexes';
end loop;

end;
/

As with the former procedure, the online keywork only works in enterprise editions. And I have a little Failsave, that it only takes partitions in the ARCHIVE Tablespace into account.

By adjusting the Where clauses you can easily transform this to work with multiple tables, different high values, etc.

Now that you have these procedures, call them in a scheduler job, cronjob or whatever you desire.

I welcome your comments.