Arup Nanda is author of
Oracle Privacy Security Auditing by Rampant TechPress
From the Arup Nanda Blog: Confessions of an Oracle DBA Junkie
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 this and find it useful.
Feedback and Additional Points
Question: 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.
Answer: 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.
>> 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.
Question: Is that basically what interval partitioning does in 11g?
Answer: 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.
 |
If you like Oracle tuning, see the book "Oracle
Tuning: The Definitive Reference", with 950 pages of tuning tips and
scripts.
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts. |
|
Download your Oracle scripts now:
www.oracle-script.com
The
definitive Oracle Script collection for every Oracle professional DBA
|
