Automatic Partition Management for Oracle 10g

If you have a moving window partitioning scheme then you should know that in Oracle 11g you now have a brand new way of creating new partitions. Oracle 11g creates the partitions in an automatic fashion, so that when the row arrives at the table, if the partition does not exist, Oracle will create it. This is awsome, but imagine two things:

* You can’t migrate to Oracle 11g on the fly. You’re stuck in 10g
* You also need to drop the oldest partition, because you have lack of storage issues

Then if you are in this situation we have the solution for you. It’s bullet-proof tested and proven at production sites.

Every night you need to add another partition and remove the oldest. Pure moving window stuff. The format of the partition name is: PXXXX_YYYY_MM_DD, where XXXX is a sequential number that increments every day. This is daily partitioning and should only have a job running this code everynight:

declare
x varchar2(90);
s varchar2(900);
begin
-- Fetchs the name of the oldest partition
select partition_name
into x
from sys.dba_tab_partitions
where partition_position = 1
and table_name = 'MYTABLE'
and table_owner = 'MYOWNER';
-- Builds the name-string
s := 'ALTER TABLE MYOWNER.MYTABLE DROP PARTITION '||x||' UPDATE INDEXES';
-- Uses a customized report and sends it by email
-- so you can see the partitions state before and after
MYOWNER.my_pkg.sends_email('REPORT6');
-- Drops the Partition
execute immediate s;
-- And now adds another
MYOWNER.add_partition;
MYOWNER.my_pkg.sends_email('REPORT6');
--dbms_output.put_line(s);
end;

The procedure “add_partition” is another piece of automatic code that you must create prior to the previous PL/SQL block:

CREATE OR REPLACE procedure MYOWNER.add_partition
is
next_part varchar2(40);
less_than_char varchar2(20);
comando_add varchar2(1000);
BEGIN

-- Generates the name of the partition
select 'P'||to_char(to_number(substr(partition_name,2,
         instr(partition_name,'_',1)-2))+1)||'_'||
         to_char(to_date(substr(partition_name,
         instr(partition_name,'_',1)+1),'yyyy_mm_dd')+1,'yyyy_mm_dd'),
         replace(to_char(to_date(substr(partition_name,
        instr(partition_name,'_',1)+1),'yyyy_mm_dd')+2,'yyyy_mm_dd'),'_','-')
into next_part,less_than_char
from dba_tab_partitions
where table_owner = 'MYOWNER'
and table_name = 'MYTABLE'
and partition_position = (select max(partition_position)
from dba_tab_partitions where table_owner = 'MYOWNER'
and table_name = 'MYTABLE');
-- Builds the statement string
comando_add :=              'ALTER TABLE MYOWNER.MYTABLE ADD PARTITION '||next_part;
comando_add := comando_add||' VALUES LESS THAN (to_date('||chr(39)||less_than_char;
comando_add := comando_add||chr(39)||','||chr(39)||
'yyyy-mm-dd'||chr(39)||')) TABLESPACE DATA_PARTITIONED';
-- Executes the statement
execute immediate(comando_add);
--dbms_output.put_line(comando_add);
end;
/

You will have permission issues that you can resolve reading this.

Source: http://ocpdba.wordpress.com/2009/10/12/automatic-partition-management-for-oracle-10g/