Interval Partitioning in Oracle 11g

In Oracle 11g wurde das Interval Partitioning eingeführt, welches auf Range Partitioning basiert, allerdings werden die Partitionen automatisch auf Basis eines Intervals angelegt.

CONN / AS SYSDBA
SET LINESIZE 500
SET ECHO ON
CL SCR
CREATE TABLESPACE TBSMW1 DATAFILE 'TBSMW1_01.DBF' SIZE 1M;
CREATE TABLESPACE TBSMW2 DATAFILE 'TBSMW2_01.DBF' SIZE 1M;
CREATE TABLESPACE TBSMW3 DATAFILE 'TBSMW3_01.DBF' SIZE 1M;
CREATE TABLE MESSWERTE
(
    ID NUMBER,
    WERT NUMBER(10,2),
    ERSTELLUNGSDATUM DATE
)
PARTITION BY RANGE(ERSTELLUNGSDATUM)
INTERVAL (NUMTOYMINTERVAL(1,'YEAR')) STORE IN (TBSMW1,TBSMW2,TBSMW3)
(
 PARTITION MWP1 VALUES LESS THAN (TO_DATE('01.01.2006','DD.MM.YYYY')) TABLESPACE TBSMW1,
 PARTITION MWP2 VALUES LESS THAN (TO_DATE('01.01.2007','DD.MM.YYYY')) TABLESPACE TBSMW2,
 PARTITION MWP3 VALUES LESS THAN (TO_DATE('01.01.2008','DD.MM.YYYY')) TABLESPACE TBSMW3
);

PAUSE

INSERT INTO MESSWERTE VALUES (1,'4711',TO_DATE('01.01.2005','DD.MM.YYYY'));
INSERT INTO MESSWERTE VALUES (2,'4712',TO_DATE('01.01.2006','DD.MM.YYYY'));
INSERT INTO MESSWERTE VALUES (3,'4713',TO_DATE('01.01.2007','DD.MM.YYYY'));

SELECT PARTITION_NAME, HIGH_VALUE, TABLESPACE_NAME
FROM ALL_TAB_PARTITIONS
WHERE TABLE_NAME='MESSWERTE' ORDER BY PARTITION_NAME;

PAUSE

INSERT INTO MESSWERTE VALUES (4,'4714',TO_DATE('01.01.2008','DD.MM.YYYY'));
INSERT INTO MESSWERTE VALUES (5,'4715',TO_DATE('01.01.2009','DD.MM.YYYY'));
INSERT INTO MESSWERTE VALUES (6,'4716',TO_DATE('01.01.2010','DD.MM.YYYY'));
COMMIT;

SELECT PARTITION_NAME, HIGH_VALUE, TABLESPACE_NAME
FROM ALL_TAB_PARTITIONS
WHERE TABLE_NAME='MESSWERTE' ORDER BY PARTITION_NAME;

PAUSE

DROP TABLE MESSWERTE PURGE;
DROP TABLESPACE TBSMW1 INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE TBSMW2 INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE TBSMW3 INCLUDING CONTENTS AND DATAFILES;