Handling Partitioned Tables

Subject: We have lots of partitioned tables to handle manually. Every begining of the month or year we have add new partitions to prevent "ORA-14400: inserted partition key does not map to any partition". As we have a increasing number of partitioned tables, we faced this problem increasingly for last months.

I decided to write a plsql script that runs in period by a scheduled job. You can find the scheduled job settings in my previous postings. In this posting i will share the procedure that automatically creates the new months partition, archives, compresses and deletes the older partitions by the supplied retention.

CREATE OR REPLACE procedure AVEA_SYSMON.p_check_partition2
  (s_table_name IN varchar2, 
   s_schema_name IN varchar2, 
   s_archive_ts IN varchar2, 
   n_archive_ret number, 
   n_delete_ret number) 
/*As i run this procedure from a job. I want running user to own the privileges*/
AUTHID CURRENT_USER 
AS 
  s_compression varchar2(20);
  s_ts_name varchar2(20);
  
  n_max_month number;
  n_max_year number;
    
  n_next_month number;
  n_next_year number;
  
  n_this_year number;
  n_this_month number;
  
  part_next_month number;
  part_next_year number;
  
  n_ctl number;     
  
  d_next_month date;
  s_part_less_than varchar2(200);
  s_cmd varchar2(200);   
begin
  
  /*I will read the dba_tab_partitions management view to find the table partitions. 
  This procedure works with the formatted name of the partition. 
  Here you should have YYYYMM at the end of the partition name*/
  select 
    max(substr(partition_name,-6,4)) into n_max_year
  from 
    dba_tab_partitions t1 
  where 
    table_owner=s_schema_name and 
    table_name=s_table_name;
  
  -- get max month of table partition
  select 
    max(substr(partition_name,-2,2)) into n_max_month
  from 
    dba_tab_partitions t1 
  where 
    table_owner=s_schema_name and 
    table_name=s_table_name and 
    substr(partition_name,-6,4) = n_max_year;        

  -- find this month this year;
  n_this_year := to_number(to_char(sysdate,'YYYY'));
  n_this_month := to_number(to_char(sysdate,'MM'));
  
  -- find next month next year
  n_next_year := to_number(to_char(add_months(sysdate,1),'YYYY'));
  n_next_month := to_number(to_char(add_months(sysdate,1),'MM'));

  -- partition definition for next month next year should be one more
  part_next_year := to_number(to_char(add_months(sysdate,2),'YYYY'));
  part_next_month :=  to_number(to_char(add_months(sysdate,2),'MM'));

  
  -- check if next months partition exists, create it if not exists

if mod(n_this_month,12) = mod(n_max_month,12) then

s_part_less_than := 'to_date(''01.' || trim(to_char(part_next_month,'09')) || '.' || part_next_year || ''',''DD.MM.YYYY'')';

s_cmd:='alter table ' || s_schema_name || '.' || s_table_name || ' add partition ' || s_table_name || '_' || n_next_year || '_' || trim(to_char(n_next_month,'09')) || ' values less than (' || s_part_less_than || ') ';

      --dbms_output.put_line(s_cmd);
      begin
        execute immediate (s_cmd);
      exception
        when others then
          null;
      end; 
  end if;
  
  /*compress and move partitions to archive tablespace which are  
  months older*/
  begin    
      select 
        compression, tablespace_name into s_compression, s_ts_name
      from 
        dba_tab_partitions 
      where 
        table_owner=s_schema_name and 
        table_name=s_table_name and 
        substr(partition_name,-6,4)=to_char(add_months(sysdate,n_archive_ret),'YYYY') and 
        substr(partition_name,-2,2)=to_char(add_months(sysdate,n_archive_ret),'MM');
      
      /*first i should be sure if the partition to be compressed and archived 
      is exists. And move the partition to archive tablespace if it is not already 
      there and compress if it is not already compresses*/
      if s_ts_name!=s_archive_ts then
         s_cmd := 'alter table ' || s_schema_name || '.' || s_table_name || 
                  ' move partition ' || s_table_name || '_' || 
                  trim(to_char(add_months(sysdate,n_archive_ret),'YYYY')) || '' || 
                  trim(to_char(add_months(sysdate,n_archive_ret),'MM')) || 
                  ' tablespace ' || s_archive_ts;
         if s_compression!='ENABLED' then
           s_cmd := s_cmd || ' COMPRESS';              
        end if;
        s_cmd := s_cmd || ' update global indexes';
        execute immediate (s_cmd);
      end if;           
 
      s_cmd:='';
  exception
    when no_data_found then 
        null;        
  end;
  
  -- drop partitions of  year old
  select 
    count(*) into n_ctl
  from 
    dba_tab_partitions 
  where 
    table_owner=s_schema_name and 
    table_name=s_table_name and 
    substr(partition_name,-6,4)=to_char(add_months(sysdate,n_delete_ret),'YYYY') and 
    substr(partition_name,-2,2)=to_char(add_months(sysdate,n_delete_ret),'MM');  
  
  /* if the partition to be dropped exists then drop it */
  if n_ctl=1 then
      s_cmd := 'alter table ' || s_schema_name || '.' || s_table_name || 
               ' drop partition ' || s_table_name || '_' || 
               to_char(add_months(sysdate,n_delete_ret),'YYYY') || '' || 
               to_char(add_months(sysdate,n_delete_ret),'MM') || 
               ' update global indexes';
     
      execute immediate (s_cmd);
  end if; 
   
end p_check_partition2;
/

The procedure is OK now. You should now be careful to give the right parameters. Be sure not to drop something should not be dropped. In our site our delete retention is one year and archive retention is 3 months. I run this procedure like the following parameters;

begin
  p_check_partition2('PAYFORME','PQ','PQ_ARCHIVE',-3,-12);
end;