There is another trick you can use: exchange the subpartition with a table that has the extent sizes you want:
create table t (
c1 int, c2 int
) partition by range ( c1 )
subpartition by list ( c2 )
subpartition template (
subpartition sp1 values ( 1 ),
subpartition sp2 values ( 2 )
) (
partition p1 values less than ( 10 ) storage ( initial 32k ),
partition p2 values less than ( 20 ) storage ( initial 32k )
);
create table exch (
c1 int, c2 int
) storage ( initial 128k );
select partition_name, subpartition_name, initial_extent
from user_tab_subpartitions;
PARTITION_NAME SUBPARTITION_NAME INITIAL_EXTENT
P1 P1_SP1 32768
P1 P1_SP2 32768
P2 P2_SP1 32768
P2 P2_SP2 32768
alter table t
exchange subpartition p1_sp1
with table exch;
select partition_name, subpartition_name, initial_extent
from user_tab_subpartitions;
PARTITION_NAME SUBPARTITION_NAME INITIAL_EXTENT
P1 P1_SP1 131072
P1 P1_SP2 32768
P2 P2_SP1 32768
P2 P2_SP2 32768 Of course, this will also swap the data. So this only works with empty or static subpartitions so you can load the data into the other table for exchange first.