Thursday, November 11, 2010

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.

12 comments:

Marco V. said...

Thanks Arup.
Great job,
MarcoV

Flado said...

Hi Arup,

Where does "l_sched_rec.num_parts_to_create" come from? It is not in the partition_create_schedule table.

Also, the tool is leaving all index partitions it rebuilds in the NOLOGGING state, even if they were LOGGING before.

And what if my MAXVALUE partition is not named PMAX? I mean, I could adjust the script to use my name, but I might have different names for the MAXVALUE partitions of different tables. Now what?

INITRANS, FREELISTS and FREELIST GROUPS wouldn't be adjustable, if the values (respectively) 11, 11, and 7 were the optimal ones.

And maybe I'm reading the code wrong, but it appears the script would add a daily partition each time it is run (and other partitions if run on the first of the respective period). So, if it succeeds for some tables but fails later, I cannot simply re-run it, since this would create extra daily (and possibly weekly etc.) partitions, moving my highest partition a period in the future. Do this enough times, and I may find myself creating daily partitions a year ahead.

And failing is not unlikely. A single active transaction against any of the PMAX partitions would crash the whole procedure. Sure, I would get the email, but re-running the script is not trivial.

Cheers,
Flado

Arup said...

@Flado - thanks for catching it. I had that functionality but decided to pull it out pending further testing. But did a sloppy job at that - I pulled it from the table but not from the code. But now that I think about it, it's better to be there. It's the number of units you want to create in advance. For instance it's a DAILY partition, you may want the next daily partition to be 3 days from now instead of just 1. In that case you would put 3 there; not 1. That specific functionality will be enhanced over time.

I have changed the table creation and the insert statements accordingly. No change has been made to the procedure.

Arup said...

@Flado on your specific comments:

>> Also, the tool is leaving all index partitions it rebuilds in the NOLOGGING state, even if they were LOGGING before.

True. In the next version it will check for the previous state and reinstate that.

>> INITRANS, FREELISTS and FREELIST GROUPS wouldn't be adjustable, if the values (respectively) 11, 11, and 7 were the optimal ones.

Yes; that's an assumption I made. These values will most likely be the same across all indexes anyway.

>> And maybe I'm reading the code wrong, but it appears the script would add a daily partition each time it is run (and other partitions if run on the first of the respective period). So, if it succeeds for some tables but fails later, I cannot simply re-run it, since this would create extra daily

No; you are not reading the code wrong. The script would create exactly that. However, the workaround I propose is temporarily removing the table name from the driver table for the successful ones. I agree it's not the best option; but it works. I will introduce the functionality for the next iteration of enhancements.

Thanks once again for the detailed feedback. They were quite helpful.

Arup said...

This is a comment I got from @dannorris via twitter.

>> is that basically what interval partitioning does in 11g?

My response: no; it's a little different.

(1) interval partitioning creates partitions at runtime; not in advance. So, if you know that partitions will need to be created for the next month, before the month starts, you really can't do that in interval partitioning. You will need to insert a dummy record for the next month's date for the partition to be created. This tool, on the other hand can create that in advance.

(2) Interval partitioning is only for 11g; this tool is for all versions - 10g. I haven't tried in 9i; but it should work too.

Amit said...

While executing prcedure getting error message


SQL> exec partition_create
Owner Table Part Unit Old HV New HV Exec
--------- ----------------------------- --------- ---------- ---------- ---
BEGIN partition_create; END;

*
ERROR at line 1:
ORA-20001: ORA-01861: literal does not match format string
ORA-06512: at "SYS.PARTITION_CREATE", line 245
ORA-06512: at line 1


Any idea ?

Thanks
Amit Saini

Amit said...

IN our case partitions are done with VALUES LESS THAN ('110201')

where 110201 is YYDDMM.


Thanks
Amit Saini

Aidan Harding said...

This is a interesting article. I myself use CSS Friendly Control Adapters. They are not perfect but at least give me some control over menu style. Don't care much about access keys but I'm going to use Listing

Sam said...

how can i obtain the host name for the related database name??

Arup said...

@Sam There are two options:

SQL> select sys_context('USERENV','HOST') from dual;

SYS_CONTEXT('USERENV','HOST')
--------------------------------------------------------------------------------
oradba2

1 row selected.

SQL> select HOST_NAME from v$instance;

HOST_NAME
----------------------------------------------------------------
oradba2

1 row selected.

Sam said...

Arup....

Here is the query im using...

select DB_NAME,a.DBID,input_type "Backup Object",STATUS,max(start_time) "Backup Begin",max(end_time) "Backup End"
from rman.rc_rman_backup_job_details bkp,(select b.db_key,b.dbid from rman.rc_database b) a
where
start_time > sysdate - 1 and input_type in ('DB INCR','DB FULL','ARCHIVELOG','BACKUPSET')
and a.db_key=bkp.db_key
group by DB_NAME,a.DBID,STATUS,input_type
order by 1
/

i would like to get the hostname of the corresponding database name using any of the repository view i.e rc_* of a target database in the above query....

Sam said...

Arup...Any update on my next question?

Translate