well, you only specified the list partitioning scheme for P0, not for subsequently created partitions. You would do that with the template.
ops$tkyte%ORA11GR2> create table t(x number,y number, z number)
2 partition by range(x) interval(1)
3 subpartition by list(y)
4 subpartition template
5 (
6 subpartition sp0 values ( 0 ),
7 subpartition sp1 values ( 1 ),
8 subpartition sp2 values ( 2 ),
9 subpartition sp3 values ( 3 ),
10 subpartition sp4 values ( 4 ),
11 subpartition sp5 values ( 5 ),
12 subpartition sp6 values ( 6 )
13 )
14 (
15 partition p0 values less than (1)
16 );
Table created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> insert into t(x,y,z)
2 select 1,rownum,1
3 from all_users
4 where rownum <= 5;
5 rows created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> select partition_name,
2 SUBPARTITION_NAME,
3 HIGH_VALUE
4 from user_tab_subpartitions
5 where table_name ='T'
6 /
PARTITION_NAME SUBPARTITION_NAME HIGH_VALUE
------------------------------ ------------------------------ --------------------
P0 P0_SP0 0
P0 P0_SP1 1
P0 P0_SP2 2
P0 P0_SP3 3
P0 P0_SP4 4
P0 P0_SP5 5
P0 P0_SP6 6
SYS_P18113 SYS_SUBP18106 0
SYS_P18113 SYS_SUBP18107 1
SYS_P18113 SYS_SUBP18108 2
SYS_P18113 SYS_SUBP18109 3
SYS_P18113 SYS_SUBP18110 4
SYS_P18113 SYS_SUBP18111 5
SYS_P18113 SYS_SUBP18112 6
14 rows selected.
but again, you would have to know the values BEFORE hand. it doesn't make sense to use list partitioning with interval partitioning unless you KNOW what subpartitions you want statically created.
in other words - what is the use of dynamically creating a partition - without any of the list subpartitions you need. You would have to turn around and immediately create them (thus defeating the entire purpose of the interval partition! why dynamic create something - when you have to come back and manually create something else before you can use it!!!!?????)
In short, I don't see you as being a candidate for using interval partitioning here (think about it - you would have to add the partitions you want before the load anyway!!!).