* 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
* 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:
The procedure “add_partition” is another piece of automatic code that you must create prior to the previous PL/SQL block: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;
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/
