How to automate partition (Range - Seconds to Years) creation using Oracle Interval Partitioning in 11g:

How to automate partition (Range - Seconds to Years) creation using Oracle Interval Partitioning in 11g:

1. Create a composite Interval-List/Interval-Range partition table:

drop table x;
create table x (create_date date, site_id number, all_data varchar2(100))
PARTITION BY RANGE (create_date)
INTERVAL (NUMTODSINTERVAL(1,'HOUR'))
SUBPARTITION BY LIST (site_id)
(
PARTITION part_01 values LESS THAN (TO_DATE('18-SEP-2009 16:00:00','DD-MON-YYYY HH24:MI:SS'))
( SUBPARTITION part_01_01 VALUES (01),
SUBPARTITION part_01_126 VALUES (126),
SUBPARTITION part_01_132 VALUES (132),
SUBPARTITION part_01_135 VALUES (135)
),
PARTITION part_02 values LESS THAN (TO_DATE('18-SEP-2009 17:00:00','DD-MON-YYYY HH24:MI:SS'))
( SUBPARTITION part_02_01 VALUES (01),
SUBPARTITION part_02_126 VALUES (126),
SUBPARTITION part_02_132 VALUES (132),
SUBPARTITION part_02_135 VALUES (135)
),
PARTITION part_03 values LESS THAN (TO_DATE('18-SEP-2009 18:00:00','DD-MON-YYYY HH24:MI:SS'))
( SUBPARTITION part_03_01 VALUES (01),
SUBPARTITION part_03_126 VALUES (126),
SUBPARTITION part_03_132 VALUES (132),
SUBPARTITION part_03_135 VALUES (135)
),
PARTITION part_04 values LESS THAN (TO_DATE('18-SEP-2009 19:00:00','DD-MON-YYYY HH24:MI:SS'))
( SUBPARTITION part_04_01 VALUES (01),
SUBPARTITION part_04_126 VALUES (126),
SUBPARTITION part_04_132 VALUES (132),
SUBPARTITION part_04_135 VALUES (135)
),
PARTITION part_05 values LESS THAN (TO_DATE('18-SEP-2009 20:00:00','DD-MON-YYYY HH24:MI:SS'))
( SUBPARTITION part_05_01 VALUES (01),
SUBPARTITION part_05_126 VALUES (126),
SUBPARTITION part_05_132 VALUES (132),
SUBPARTITION part_05_135 VALUES (135)
),
PARTITION part_06 values LESS THAN (TO_DATE('18-SEP-2009 21:00:00','DD-MON-YYYY HH24:MI:SS'))
( SUBPARTITION part_06_01 VALUES (01),
SUBPARTITION part_06_126 VALUES (126),
SUBPARTITION part_06_132 VALUES (132),
SUBPARTITION part_06_135 VALUES (135)
),
PARTITION part_07 values LESS THAN (TO_DATE('18-SEP-2009 22:00:00','DD-MON-YYYY HH24:MI:SS'))
( SUBPARTITION part_07_01 VALUES (01),
SUBPARTITION part_07_126 VALUES (126),
SUBPARTITION part_07_132 VALUES (132),
SUBPARTITION part_07_135 VALUES (135)
),
PARTITION part_08 values LESS THAN (TO_DATE('18-SEP-2009 23:00:00','DD-MON-YYYY HH24:MI:SS'))
( SUBPARTITION part_08_01 VALUES (01),
SUBPARTITION part_08_126 VALUES (126),
SUBPARTITION part_08_132 VALUES (132),
SUBPARTITION part_08_135 VALUES (135)
)
);


2. Create bitmap LOCAL indexes:

create bitmap index idx1_x on x(create_date) local;
create bitmap index idx2_x on x(site_id) local;


3. Insert and Check the data in each partition:

truncate table x;
insert into x values(TO_DATE('18-SEP-2009 15:30:00','DD-MON-YYYY HH24:MI:SS'),01,'18/SEP/09 of 15''Th HR - SITE 01');
insert into x values(TO_DATE('18-SEP-2009 16:30:00','DD-MON-YYYY HH24:MI:SS'),126,'18/SEP/09 of 16''Th HR - SITE 126');
insert into x values(TO_DATE('18-SEP-2009 17:30:00','DD-MON-YYYY HH24:MI:SS'),132,'18/SEP/09 of 17''Th HR - SITE 132');
insert into x values(TO_DATE('18-SEP-2009 18:30:00','DD-MON-YYYY HH24:MI:SS'),135,'18/SEP/09 of 18''Th HR - SITE 125');
insert into x values(TO_DATE('18-SEP-2009 19:30:00','DD-MON-YYYY HH24:MI:SS'),01,'18/SEP/09 of 19''Th HR - SITE 01');
insert into x values(TO_DATE('18-SEP-2009 20:30:00','DD-MON-YYYY HH24:MI:SS'),126,'18/SEP/09 of 20''Th HR - SITE 126');
insert into x values(TO_DATE('18-SEP-2009 21:30:00','DD-MON-YYYY HH24:MI:SS'),132,'18/SEP/09 of 21''Th HR - SITE 132');
insert into x values(TO_DATE('18-SEP-2009 22:30:00','DD-MON-YYYY HH24:MI:SS'),135,'18/SEP/09 of 22''Th HR - SITE 135');
commit;

set linesize 200;
col all_data for a50;

SELECT * FROM x PARTITION (PART_01);
SELECT * FROM x PARTITION (PART_02);
SELECT * FROM x PARTITION (PART_03);
SELECT * FROM x PARTITION (PART_04);
SELECT * FROM x PARTITION (PART_05);
SELECT * FROM x PARTITION (PART_06);
SELECT * FROM x PARTITION (PART_07);
SELECT * FROM x PARTITION (PART_08);


4. Check and record the HIGH VALUE of the partition table:

col table_name for a10;
col partition_name for a15;
select table_name, partition_name, high_value
from user_tab_partitions
where table_name = 'X';

DB Output:
TABLE_NAME PARTITION_NAME HIGH_VALUE
---------- --------------- --------------------------------------------------------------------------------
X PART_01 TO_DATE(' 2009-09-18 16:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X PART_02 TO_DATE(' 2009-09-18 17:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X PART_03 TO_DATE(' 2009-09-18 18:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X PART_04 TO_DATE(' 2009-09-18 19:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X PART_05 TO_DATE(' 2009-09-18 20:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X PART_06 TO_DATE(' 2009-09-18 21:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X PART_07 TO_DATE(' 2009-09-18 22:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X PART_08 TO_DATE(' 2009-09-18 23:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

8 rows selected.


5. Check and record the HIGH VALUE of the Subpartition table:

col subpartition_name for a20;
col high_value for a10;
select table_name, partition_name, subpartition_name, high_value
from user_tab_subpartitions
where table_name = 'X';

DB Output:
TABLE_NAME PARTITION_NAME SUBPARTITION_NAME HIGH_VALUE
---------- --------------- -------------------- ----------
X PART_01 PART_01_135 135
X PART_01 PART_01_132 132
X PART_01 PART_01_126 126
X PART_01 PART_01_01 01
X PART_02 PART_02_135 135
X PART_02 PART_02_132 132
X PART_02 PART_02_126 126
X PART_02 PART_02_01 01
X PART_03 PART_03_135 135
X PART_03 PART_03_132 132
X PART_03 PART_03_126 126
X PART_03 PART_03_01 01
X PART_04 PART_04_135 135
X PART_04 PART_04_132 132
X PART_04 PART_04_126 126
X PART_04 PART_04_01 01
X PART_05 PART_05_135 135
X PART_05 PART_05_132 132
X PART_05 PART_05_126 126
X PART_05 PART_05_01 01
X PART_06 PART_06_135 135
X PART_06 PART_06_132 132
X PART_06 PART_06_126 126
X PART_06 PART_06_01 01
X PART_07 PART_07_135 135
X PART_07 PART_07_132 132
X PART_07 PART_07_126 126
X PART_07 PART_07_01 01
X PART_08 PART_08_135 135
X PART_08 PART_08_132 132
X PART_08 PART_08_126 126
X PART_08 PART_08_01 01

32 rows selected.


6. Insert new record (Next day 15'th Hour) and validate the partition creation:

insert into x values(TO_DATE('19-SEP-2009 15:30:00','DD-MON-YYYY HH24:MI:SS'),01,'19/SEP/09 of 15''Th HR - SITE 01');
commit;

col high_value for a10
select table_name, partition_name, subpartition_name, high_value
from user_tab_subpartitions
where table_name = 'X';

DB Output:
TABLE_NAME PARTITION_NAME SUBPARTITION_NAME HIGH_VALUE
---------- --------------- -------------------- ----------
..
..
..

X PART_08 PART_08_01 01
X SYS_P42 SYS_SUBP41 DEFAULT <------- New Partition

33 rows selected.

col high_value for a80
select table_name, partition_name, high_value
from user_tab_partitions
where table_name = 'X';

DB Output:
TABLE_NAME PARTITION_NAME HIGH_VALUE
---------- --------------- --------------------------------------------------------------------------------
X PART_01 TO_DATE(' 2009-09-18 16:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X PART_02 TO_DATE(' 2009-09-18 17:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X PART_03 TO_DATE(' 2009-09-18 18:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X PART_04 TO_DATE(' 2009-09-18 19:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X PART_05 TO_DATE(' 2009-09-18 20:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X PART_06 TO_DATE(' 2009-09-18 21:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X PART_07 TO_DATE(' 2009-09-18 22:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X PART_08 TO_DATE(' 2009-09-18 23:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X SYS_P42 TO_DATE(' 2009-09-19 16:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA <------- New Partition

9 rows selected.


7. Insert new record(Next day 17'th Hour) and validate the partition creation:

insert into x values(TO_DATE('19-SEP-2009 17:30:00','DD-MON-YYYY HH24:MI:SS'),132,'19/SEP/09 of 17''Th HR - SITE 132');
commit;

col high_value for a10
select table_name, partition_name, subpartition_name, high_value
from user_tab_subpartitions
where table_name = 'X';

DB Output:
TABLE_NAME PARTITION_NAME SUBPARTITION_NAME HIGH_VALUE
---------- --------------- -------------------- ----------
..
..
..
X PART_08 PART_08_01 01
X SYS_P42 SYS_SUBP41 DEFAULT
X SYS_P44 SYS_SUBP43 DEFAULT <------- New Partition

34 rows selected.

col high_value for a80
select table_name, partition_name, high_value
from user_tab_partitions
where table_name = 'X';

DB Output:
TABLE_NAME PARTITION_NAME HIGH_VALUE
---------- --------------- --------------------------------------------------------------------------------
X PART_01 TO_DATE(' 2009-09-18 16:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X PART_02 TO_DATE(' 2009-09-18 17:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X PART_03 TO_DATE(' 2009-09-18 18:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X PART_04 TO_DATE(' 2009-09-18 19:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X PART_05 TO_DATE(' 2009-09-18 20:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X PART_06 TO_DATE(' 2009-09-18 21:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X PART_07 TO_DATE(' 2009-09-18 22:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X PART_08 TO_DATE(' 2009-09-18 23:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X SYS_P42 TO_DATE(' 2009-09-19 16:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X SYS_P44 TO_DATE(' 2009-09-19 18:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA <------- New Partition

10 rows selected.


8. Insert new record (Next day 16'th Hour) and validate the partition creation:
# This record data is one hour less than the previous record in Step 7.
# No problem with creating the partition even with jumbled hourly data!!!!

insert into x values(TO_DATE('19-SEP-2009 16:30:00','DD-MON-YYYY HH24:MI:SS'),126,'19/SEP/09 of 16''Th HR - SITE 126');
commit;

col high_value for a10
select table_name, partition_name, subpartition_name, high_value
from user_tab_subpartitions
where table_name = 'X';

DB Output:
TABLE_NAME PARTITION_NAME SUBPARTITION_NAME HIGH_VALUE
---------- --------------- -------------------- ----------
..
..
..
X PART_08 PART_08_01 01
X SYS_P42 SYS_SUBP41 DEFAULT
X SYS_P44 SYS_SUBP43 DEFAULT
X SYS_P46 SYS_SUBP45 DEFAULT <------- New Partition

35 rows selected.

col high_value for a80
select table_name, partition_name, high_value
from user_tab_partitions
where table_name = 'X';

DB Output:
TABLE_NAME PARTITION_NAME HIGH_VALUE
---------- --------------- --------------------------------------------------------------------------------
X PART_01 TO_DATE(' 2009-09-18 16:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X PART_02 TO_DATE(' 2009-09-18 17:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X PART_03 TO_DATE(' 2009-09-18 18:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X PART_04 TO_DATE(' 2009-09-18 19:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X PART_05 TO_DATE(' 2009-09-18 20:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X PART_06 TO_DATE(' 2009-09-18 21:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X PART_07 TO_DATE(' 2009-09-18 22:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X PART_08 TO_DATE(' 2009-09-18 23:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X SYS_P42 TO_DATE(' 2009-09-19 16:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X SYS_P44 TO_DATE(' 2009-09-19 18:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X SYS_P46 TO_DATE(' 2009-09-19 17:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA <------- New Partition

11 rows selected.


Side Note:
If you want to create different intervals, here are the available options:

NUMTODSINTERVAL - An Interval Day to Second:

'DAY' - INTERVAL (NUMTODSINTERVAL(1,'DAY')) or INTERVAL (NUMTODSINTERVAL(7,'DAY'))
'HOUR' - INTERVAL (NUMTODSINTERVAL(1,'HOUR')) or INTERVAL (NUMTODSINTERVAL(8,'HOUR'))
'MINUTE' - INTERVAL (NUMTODSINTERVAL(1,'MINUTE')) or INTERVAL (NUMTODSINTERVAL(15,'MINUTE'))
'SECOND' - INTERVAL (NUMTODSINTERVAL(1,'SECOND')) or INTERVAL (NUMTODSINTERVAL(30,'SECOND'))


NUMTOYMINTERVAL - An Interval Year to Month:

'YEAR' - INTERVAL (NUMTOYMINTERVAL(1,'YEAR')) or INTERVAL (NUMTOYMINTERVAL(3,'YEAR'))
'MONTH' - INTERVAL (NUMTOYMINTERVAL(1,'MONTH')) or INTERVAL (NUMTOYMINTERVAL(4,'MONTH'))