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.
If you need any further help applying this, please let me know.