Tool to Add Range Partitions Automatically to a Table

Continuing on my earlier posts on partitioning, here are is another tool on this topic. If you have been using partitioned tables, you might have noticed that one of the thorny issues in partitioning is the need to create partitions. If you don't have the partition ready, the inserts will fail with the following message

ERROR at line 1:
ORA-14400: inserted partition key does not map to any partition

This error is pretty disastrous to applications as they essentially stop functioning, causing outage, until you add the necessary partition. What can you do to prevent it? Maintain a unblinking vigil, of course, which may quickly become impractical. Oracle 11g has a nice answer - the INTERVAL partitioning, which automatically creates partitions when the new records come in. What if you are on 10g or do not want to use INTERVAL partitioning for some reason? Is there a way to simulate the interval partitioning?

After getting frustrated with the lack of proper partitions more than once, I decided to develop this tool. I perfected the tool over several months to make it more generic. Here it is. Enjoy.

Important: This is provided for educational purpose only. Use it at your own risk. There is no implied or expressed warranty and the author assumes no responsibility for any adverse effect.

What the Tool does

The tool is implemented as a stored procedure. When executed, it reads a driver table called partition_create_schedule, which records the tables to which the partitions are to be added. The table is created as shown below:

/* ---------------------------------------------------------------------
Name        : partition_create_schedule
Purpose     : To store the retention period of partitions of
            : indivisual tables. This data is used by partition_drop
            : procedure to automatically drop partitions.
----------------------------------------------------------------------*/

create table partition_create_schedule
(
 OWNER                  VARCHAR2(30),
 TABLE_NAME             VARCHAR2(30),
 PARTITION_UNIT         VARCHAR2(15),
 NUM_PARTS_TO_CREATE    NUMBER(4)
)
/
comment on table partition_create_schedule is 'To record the tables for which partitions are to be added automatically'
/
comment on  column partition_create_schedule.owner is 'the owner of the table'
/
comment on  column partition_create_schedule.table_name is 'the name of the table'
/
comment on  column partition_create_schedule.PARTITION_UNIT is 'the partitioning scheme - MONTHLY/DAILY/WEEKELY/QUARTERLY/YEARLY'
/
comment on column partition_create_schedule.num_parts_to_create is 'the number of units to skip in creating the partition. For instance, if you want to create the next partition 3 days from now for a DAILY unit, place 3 here'
/

The partitioning unit is important. Remember, in date-range partitioned tables, you merely provide the range; not whether the range is monthly, weekly or something like that. Even if you have a somewhat similar range, there is no guarantee that it will remain so. You may decide to split some partition or coalesce two. Therefore it is important that you tell the tool what type of partitioning duration the table is under.

Let's insert some records:

insert into partition_create_schedule values ('ARUP','PTEST1','MONTHLY',1);
insert into partition_create_schedule values ('ARUP','PTEST2','MONTHLY',1);
insert into partition_create_schedule values ('ARUP','PTEST3','QUARTERLY',1);
insert into partition_create_schedule values ('ARUP','PTEST4','DAILY',1);
commit;

Now comes the tool - the procedure. To build it, I considered these objectives:

(1) The intervals could be daily, weekly, monthly, quarterly and yearly
(2) When this procedure executes, it should automatically compute the boundary of the partition to add from the existing partitions. This should not be expected to be input from the user.
(3) This procedure will be executed every day automatically. In case of a DAILY partition, a new partition will be created; but not for other ranges. For weekly ranges, the new partition should be created only on the first day of the week; for monthly, on the first day of the month, etc.
(4) The global indexes must be updated automatically
(5) It must handle the two possibilities of the existing highest partition, e.g. a partition with a distinct boundary value and one with (MAXVALUE) clause. In the former case, a partition is added. In the latter case, the maxvalue partition has to be "split"; not added. The procedure must take care of both cases automatically.
(6) When the partition is split, the local indexes get invalidated and must be rebuilt.
(7) It should send an email to the DBA in both success and failure.

The tool handles all the above objectives pretty well.

CREATE OR REPLACE procedure SYS.partition_create
as
 cursor sched_cur is
  select * 
  from partition_create_schedule
  ;
 l_sched_rec    sched_cur%rowtype;
 l_cutoff_date   date;
 l_hvc     varchar2(2000);
 l_new_hvc    varchar2(2000);
 l_old_hvc    varchar2(2000);
 l_part_name    varchar2(2000);
 l_new_part_name   varchar2(2000);
 l_table_name   varchar2(2000);
 l_hv     date;
 l_new_hv    date;
 l_sql     varchar2(2000);
 l_full     varchar2(32000);
 l_ts     varchar2(2000);
 l_errm     varchar2(2000);
 l_mail_body    varchar2(2000);
 l_out     varchar2(2000);
 l_recipients   varchar2(2000) :=
  'dba.ops@mycompany.com';
 errc     number(10);
 l_place     varchar2(2);
 l_exec_flag    varchar2(4);
 invalid_partition_unit exception;
begin
 execute immediate 'alter session set smtp_out_server = ''smtp.mycompany.com''';
 l_out :=
  rpad('Owner',10)||
  rpad('Table',30)||
  rpad('Part Unit',10)||
  rpad('Old HV',11)||
  rpad('New HV',11)||
  rpad('Exec',4)
  ;
 dbms_output.put_line(l_out);
 l_out :=
  rpad('-',9,'-')||' '||
  rpad('-',29,'-')||' '||
  rpad('-',9,'-')||' '||
  rpad('-',10,'-')||' '||
  rpad('-',10,'-')||' '||
  rpad('-',3,'-')
  ;
 dbms_output.put_line(l_out);
 open sched_cur;
 loop --{
  fetch sched_cur into l_sched_rec;
  exit when sched_cur%notfound;
  select high_value, tablespace_name
  into l_old_hvc, l_ts
  from dba_tab_partitions tp
  where partition_position = (
    select max(partition_position) from dba_tab_partitions
    where table_name = tp.table_name
    and table_owner = tp.table_owner
  )
  and table_name = l_sched_rec.table_name
  and table_owner = l_sched_rec.owner;
  if (l_old_hvc = 'MAXVALUE') then --{
   -- PMAX code. Split the partition
   -- first get the hign value of the partition just prior to PMAX
   select high_value, tablespace_name
   into l_hvc, l_ts
   from dba_tab_partitions tp
   where partition_position = (
     select max(partition_position) - 1 from dba_tab_partitions
     where table_name = tp.table_name
     and table_owner = tp.table_owner
   )
   and table_name = l_sched_rec.table_name
   and table_owner = l_sched_rec.owner;
   execute immediate 'select '||l_hvc||' from dual' into l_hv;
   l_exec_flag := 'NO';
   case l_sched_rec.partition_unit  --{
    when 'DAILY' then
     l_new_hv := l_hv + l_sched_rec.num_parts_to_create;
     l_new_part_name := 'Y'||to_char(l_new_hv,'YY')||'M'||to_char(l_new_hv,'MM')||'D'||to_char(l_new_hv,'DD');
     l_exec_flag := 'YES';
    when 'WEEKLY' then
     l_new_hv := l_hv + (7*l_sched_rec.num_parts_to_create);
     l_new_part_name := 'Y'||to_char(l_new_hv,'YY')||'W'||to_char(l_new_hv,'WW');
     if (to_char(sysdate,'d') = '1') then
      l_exec_flag := 'YES';
     end if;
    when 'MONTHLY' then
     l_new_hv := add_months(l_hv,l_sched_rec.num_parts_to_create);
     if (to_char(sysdate,'dd') = '1') then
      l_exec_flag := 'YES';
     end if;
     l_new_part_name := 'Y'||to_char(l_new_hv,'YY')||'M'||to_char(l_new_hv,'MM');
    when 'QUARTERLY' then
     l_new_hv := add_months(l_hv,3*l_sched_rec.num_parts_to_create);
     l_new_part_name := 'Y'||to_char(l_new_hv,'YY')||'Q'||to_char(l_new_hv,'Q');
     if (to_char(sysdate,'mm/dd') in ('01/01','04/01','07/01','10/01')) then
      l_exec_flag := 'YES';
     end if;
    when 'ANNUALLY' then
     l_new_hv := add_months(l_hv,12*l_sched_rec.num_parts_to_create);
     l_new_part_name := 'Y'||to_char(l_new_hv,'YY');
     if to_char(sysdate,'mm/dd') = '01/01' then
      l_exec_flag := 'YES';
     end if;
    else
     raise invalid_partition_unit; 
   end case; --}
   l_new_hvc := to_char(l_new_hv,'mm/dd/yyyy');
   l_sql := 'alter table '||
    l_sched_rec.owner||'.'||
    l_sched_rec.table_name||
    ' split partition pmax '||
    ' at (to_date('''||l_new_hvc||
    ''',''mm/dd/yyyy''))'||
    ' into ( partition '||l_new_part_name||
    ' tablespace '||l_ts||
    ' initrans 11 storage (freelists 11 freelist groups 7)'||
    ' , partition pmax) update global indexes';
  else --}{
   -- non PMAX code. Add the partition
   l_hvc := l_old_hvc;
   execute immediate 'select '||l_hvc||' from dual' into l_hv;
   l_exec_flag := 'NO';
   case l_sched_rec.partition_unit  --{
    when 'DAILY' then
     l_new_hv := l_hv + l_sched_rec.num_parts_to_create;
     l_new_part_name := 'Y'||to_char(l_new_hv,'YY')||'M'||to_char(l_new_hv,'MM')||'D'||to_char(l_new_hv,'DD');
     l_exec_flag := 'YES';
    when 'WEEKLY' then
     l_new_hv := l_hv + (7*l_sched_rec.num_parts_to_create);
     l_new_part_name := 'Y'||to_char(l_new_hv,'YY')||'W'||to_char(l_new_hv,'WW');
     if (to_char(sysdate,'d') = '1') then
      l_exec_flag := 'YES';
     end if;
    when 'MONTHLY' then
     l_new_hv := add_months(l_hv,l_sched_rec.num_parts_to_create);
     if (to_char(sysdate,'dd') = '1') then
      l_exec_flag := 'YES';
     end if;
     l_new_part_name := 'Y'||to_char(l_new_hv,'YY')||'M'||to_char(l_new_hv,'MM');
    when 'QUARTERLY' then
     l_new_hv := add_months(l_hv,3*l_sched_rec.num_parts_to_create);
     l_new_part_name := 'Y'||to_char(l_new_hv,'YY')||'Q'||to_char(l_new_hv,'Q');
     if (to_char(sysdate,'mm/dd') in ('01/01','04/01','07/01','10/01')) then
      l_exec_flag := 'YES';
     end if;
    when 'ANNUALLY' then
     l_new_hv := add_months(l_hv,12*l_sched_rec.num_parts_to_create);
     l_new_part_name := 'Y'||to_char(l_new_hv,'YY');
     if to_char(sysdate,'mm/dd') = '01/01' then
      l_exec_flag := 'YES';
     end if;
    else
     raise invalid_partition_unit; 
   end case; --}
   l_new_hvc := to_char(l_new_hv,'mm/dd/yyyy');
   l_sql := 'alter table '||
    l_sched_rec.owner||'.'||
    l_sched_rec.table_name||
    ' add partition '||
    l_new_part_name||
    ' values less than '||
    ' (to_date('''||l_new_hvc||
    ''',''mm/dd/yyyy''))'||
    ' tablespace '||l_ts||
    ' initrans 11 storage (freelists 11 freelist groups 7)';
  end if; --}
  l_out :=
   rpad(l_sched_rec.owner,10)||
   rpad(l_sched_rec.table_name,30)||
   rpad(l_sched_rec.partition_unit,10)||
   rpad(to_char(l_hv,'mm/dd/yyyy'),11)||
   rpad(l_new_hvc,11)||
   rpad(l_exec_flag,4)
  ;
  dbms_output.put_line(l_out);
  if (l_exec_flag = 'YES') then --{
   dbms_output.put_line('l_new_part_name='||l_new_part_name);
   dbms_output.put_line('l_new_hvc='||l_new_hvc);
   l_mail_body := 'Statement Executed:'||l_sql;
         dbms_output.put_line('l_mail_body='||l_mail_body);
   begin
    execute immediate l_sql;
             l_mail_body := l_mail_body ||'...OK';
   exception
    when OTHERS then
     l_errm := SQLERRM;
     l_mail_body := l_mail_body||' ERROR='||l_errm;
     raise;
   end;
   -- table partition split. Now index.
   for ind_cur in (
    select index_owner, index_name, partition_name
    from dba_ind_partitions
    where (index_owner, index_name) in (
     select owner, index_name
     from dba_indexes
     where table_name = l_sched_rec.table_name
     and table_owner = l_sched_rec.owner
    )
   and status = 'UNUSABLE'
   ) loop --{
    dbms_output.put_line('Index Part='||ind_cur.index_owner||'.'||ind_cur.index_name||'.'||ind_cur.partition_name);
    l_sql := 'alter index '||
     ind_cur.index_owner||'.'||
     ind_cur.index_name||' rebuild partition '||
     ind_cur.partition_name||' nologging online';
    l_mail_body := l_mail_body||chr(12)||
     'Statement Executed:'||l_sql;
             dbms_output.put_line('l_mail_body='||l_mail_body);
    begin
     execute immediate l_sql;
        l_mail_body := l_mail_body||'...OK';
    exception
     when OTHERS then
         l_errm := SQLERRM;
         l_mail_body := l_mail_body||' ERROR='||l_errm;
      raise;
    end;
   end loop; --}
   -- index partitions made usable
  end if; --}
 end loop; --}
 close sched_cur;
 dbms_output.put_line(l_full);
 utl_mail.send(
  'oracle@prodb1',l_recipients,
  null, null,
  'Succeeded: PROPRD1 Partition Create',
  l_mail_body
 );
exception
 when OTHERS then
  l_errm := SQLERRM;
  utl_mail.send(
   'oracle@prodb1',l_recipients,
   null, null,
   'Failed: PROPRD1 Partition Create',
   l_mail_body
  );
  raise_application_error(-20001,l_errm);
end;
/
show error

The code is self explanatory. Let's see some example outputs:

SQL> @ex
Owner     Table                         Part Unit Old HV     New HV     Exec
--------- ----------------------------- --------- ---------- ---------- ---
ARUP      PTEST1                        MONTHLY   02/01/2010 04/01/2010 NO
ARUP      PTEST2                        MONTHLY   02/01/2010 04/01/2010 NO
ARUP      PTEST3                        QUARTERLY 04/01/2010 10/01/2010 NO
ARUP      PTEST4                        DAILY     01/12/2010 01/14/2010 YES
l_new_part_name=Y10M01D14
l_new_hvc=01/14/2010
l_mail_body=Statement Executed:alter table ARUP.PTEST4 split partition pmax  at
(to_date('01/14/2010','mm/dd/yyyy')) into ( partition Y10M01D14 tablespace
MGMT_TABLESPACE initrans 11 storage (freelists 11 freelist groups 7) , partition
pmax) update global indexes
ARUP      PTEST5                        DAILY     01/04/2010 01/06/2010 YES
l_new_part_name=Y10M01D06
l_new_hvc=01/06/2010
l_mail_body=Statement Executed:alter table ARUP.PTEST5 add partition Y10M01D06
values less than  (to_date('01/06/2010','mm/dd/yyyy')) tablespace
MGMT_TABLESPACE initrans 11 storage (freelists 11 freelist groups 7)
ARUP      PTEST6                        DAILY     01/02/2010 01/04/2010 YES
l_new_part_name=Y10M01D04
l_new_hvc=01/04/2010
l_mail_body=Statement Executed:alter table ARUP.PTEST6 add partition Y10M01D04
values less than  (to_date('01/04/2010','mm/dd/yyyy')) tablespace
MGMT_TABLESPACE initrans 11 storage (freelists 11 freelist groups 7)
ARUP      PTEST7                        DAILY     01/02/2010 01/04/2010 YES
l_new_part_name=Y10M01D04
l_new_hvc=01/04/2010
l_mail_body=Statement Executed:alter table ARUP.PTEST7 add partition Y10M01D04
values less than  (to_date('01/04/2010','mm/dd/yyyy')) tablespace
MGMT_TABLESPACE initrans 11 storage (freelists 11 freelist groups 7)

The output is sent to the mail address recorded in the stored procedure - in case of both success and failure. Now all you have to do is to put it in a Scheduler Job and let it run every day. Populate the table PARTITION_CREATE_SCHEDULE as needed and those tables will be automatically added partitions.

Hope you enjoy and find it useful. As always, I will appreciate your feedback.