Interval Partitioning in 11g

Interval Partitioning in 11g.
Interval partitioning is an extension of range partitioning which instructs the database to automatically create partitions of a specified interval when data inserted into the table exceeds all of the existing range partitions

scott@ORCL> CREATE TABLE t
2 (
3 object_id number,
4 object_name varchar2(50),
5 created_dt date
6 )
7 partition by range(created_dt) interval(numtoyminterval(1,'MONTH'))
8 (
9 partition p1 values less than (to_date('01/01/2006','mm/dd/yyyy'))
10 ) set store in (USERS,EXAMPLE) ;

Table created.

scott@ORCL> SELECT SEGMENT_NAME,PARTITION_NAME,TABLESPACE_NAME
2 FROM USER_SEGMENTS
3 WHERE SEGMENT_NAME ='T'
4 /

SEGMENT_NAME PARTITION_NAME TABLESPACE_NAME
------------------------------ ------------------------------ --------------------
T P1 USERS

So, what happens if data inserted into table T exceeds the value 01-Jan-2006, well database creates interval partitions for data beyond that transition point.

scott@ORCL> INSERT INTO T
2 SELECT object_id,object_name,created
3 FROM all_objects;

68107 rows created.

scott@ORCL> SELECT SEGMENT_NAME,PARTITION_NAME,TABLESPACE_NAME
2 FROM USER_SEGMENTS
3 WHERE SEGMENT_NAME ='T'
4 /

SEGMENT_NAME PARTITION_NAME TABLESPACE_NAME
------------------------------ ------------------------------ --------------------
T P1 USERS
T SYS_P163 USERS
T SYS_P164 USERS

Well, what happens if Transaction rollback, the newly created partition never drops.

scott@ORCL> rollback;

Rollback complete.

scott@ORCL> select count(*) from T;

COUNT(*)
----------
0

scott@ORCL> SELECT SEGMENT_NAME,PARTITION_NAME,TABLESPACE_NAME
2 FROM USER_SEGMENTS
3 WHERE SEGMENT_NAME ='T'
4 /

SEGMENT_NAME PARTITION_NAME TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------
T P1 USERS
T SYS_P163 USERS
T SYS_P164 USERS