AUTOMATED RANGE PARTITION Author JP Vijaykumar Date 18th July 2009 /* Interval partitioning is a new partitioning feature introduced in Oracle 11g. This is an extension to range partitioning in which, Oracle automatically creates a partition when the inserted value exceeds all other partition ranges. Internal partition adds new partitions automatically, but does not drop any old partitions. Let us see, how to automate adding new partitions and drop oldest partitions to a table partitioned by range. This script was tested in Oracle 10g. */ SQL> select * from v$version; BANNER ---------------------------------------------------------------- Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64 bit PL/SQL Release 10.2.0.4.0 - Production CORE 10.2.0.4.0 Production TNS for HPUX: Version 10.2.0.4.0 - Production NLSRTL Version 10.2.0.4.0 - Production SQL> select sysdate from dual; SYSDATE --------- 03-JUL-09 /* Created a table, partitioned by range for every month. Every month 10th, a scheduled script drops the last month's partition and add a new partition for the coming month. */ drop table interval_manual_partition_jp; create table interval_manual_partition_jp ( empno number(5) NOT NULL, ename varchar2(30), hiredate date) partition by range (hiredate) ( partition p062009 values less than (to_date('2009-07-01','YYYY-MM-DD')), partition p072009 values less than (to_date('2009-08-01','YYYY-MM-DD'))) tablespace users; /* Populated records into the two existing partitions of the table interval_manual_partition_jp. */ insert into interval_manual_partition_jp values(1,'MANI',to_date('2009-06-14','YYYY-MM-DD')); insert into interval_manual_partition_jp values(2,'BABU',to_date('2009-07-07','YYYY-MM-DD')); insert into interval_manual_partition_jp values(3,'RAMU',to_date('2009-07-12','YYYY-MM-DD')); commit; /* Let us select the records from the individual partitions of the table. */ select * from interval_manual_partition_jp partition (p062009); EMPNO ENAME HIREDATE ---------- ------------------------------ --------- 1 MANI 14-JUN-09 select * from interval_manual_partition_jp partition (p072009); EMPNO ENAME HIREDATE ---------- ------------------------------ --------- 2 BABU 07-JUL-09 3 RAMU 12-JUL-09 /* Check the table_name, partition_name and high_value from dba_tab_partitions view. */ select table_name,partition_name,high_value from dba_tab_partitions where table_name='INTERVAL_MANUAL_PARTITION_JP'; 2 TABLE_NAME PARTITION_NAME ------------------------------ ------------------------------ HIGH_VALUE -------------------------------------------------------------------------------- INTERVAL_MANUAL_PARTITION_JP P062009 TO_DATE(' 2009-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA INTERVAL_MANUAL_PARTITION_JP P072009 TO_DATE(' 2009-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA /* Created index on the partitioned table. */ create index interval_manual_partition_jpix on interval_manual_partition_jp(empno) global tablespace users; /* Check the status of the index on the partitioned table. */ select index_name,status from dba_indexes where table_name='INTERVAL_MANUAL_PARTITION_JP'; INDEX_NAME STATUS ------------------------------ -------- INTERVAL_MANUAL_PARTITION_JPIX VALID /* Created the script to drop last month's partition from the partitioned table and add partition for coming month. */ set serverout on size 1000000 declare v_sq1 varchar2(1000); v_sq2 varchar2(1000); begin select 'alter table interval_manual_partition_jp drop partition p'||to_char(add_months(sysdate, -1),'mmyyyy')|| ' update global indexes' into v_sq1 from dual; dbms_output.put_line(v_sq1); execute immediate v_sq1; select 'alter table interval_manual_partition_jp add partition p'||to_char(add_months(sysdate, 1),'mmyyyy')||chr(10)|| ' values less than (to_date('''|| to_char(last_day(add_months(sysdate,1)) +1 ,'yyyy-mm-dd')||''' ,'''||'YYYY-MM-DD'||''')) ' into v_sq2 from dual; dbms_output.put_line(v_sq2); execute immediate v_sq2; exception when others then dbms_output.put_line(sqlerrm); end; / /* After executing the script, verify the table's partition particulars. */ select table_name,partition_name,high_value from dba_tab_partitions where table_name='INTERVAL_MANUAL_PARTITION_JP'; TABLE_NAME PARTITION_NAME ------------------------------ ------------------------------ HIGH_VALUE -------------------------------------------------------------------------------- INTERVAL_MANUAL_PARTITION_JP P072009 TO_DATE(' 2009-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA INTERVAL_MANUAL_PARTITION_JP P082009 TO_DATE(' 2009-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA /* After the partition table maintenance, the index status is valid. */ select index_name,status from dba_indexes where table_name='INTERVAL_MANUAL_PARTITION_JP'; INDEX_NAME STATUS ------------------------------ -------- INTERVAL_MANUAL_PARTITION_JPIX VALID /* Let us insert a record with hiredate falling under next month's range. */ insert into interval_manual_partition_jp values(4,'KRIS',to_date('2009-08-11','YYYY-MM-DD')); commit; SQL> select * from interval_manual_partition_jp partition (p062009); select * from interval_manual_partition_jp partition (p062009) * ERROR at line 1: ORA-02149: Specified partition does not exist /* Since the last month's partition was dropped, the select statement on the dropped partition failed. But the current month's partition and next month's partition are having data. */ SQL> select * from interval_manual_partition_jp partition (p072009); EMPNO ENAME HIREDATE ---------- ------------------------------ --------- 2 BABU 07-JUL-09 3 RAMU 12-JUL-09 SQL> select * from interval_manual_partition_jp partition (p082009); EMPNO ENAME HIREDATE ---------- ------------------------------ --------- 4 KRIS 11-AUG-09 /* Happy scripting. */ References: http://www.psoug.org/reference/partitions.html http://www.orafaq.com/wiki/Interval_partitioning