How to add Subpartition to all partitions

Question

I believe adding sub-partition in Oracle 11g is easy

ALTER TABLE PART_TEST
modify partition OCT19
add subpartition OCT19AXCS
values ('AXCS');

I have close to 250 partitions..namely

jan07
feb07
...
up till
...
dec26

Does it mean I need to include that many alter table statements to alter all my partitions and add sub-partitions ?

• Some Java Guy: @JSapkota I used sub-partition template at the time of initial table creation. So I guess I need to alter the complete template with newly added sub-partition..hmm No direct way to simply add only that sub-partition...OK
• Balazs Papp: @JSapkota That does not affect existing partitions.

Answer 1

You can just easily loop through the partitions in a PL/SQL loop, and add the subpartitions:

begin
 for p in (select partition_name from user_tab_partitions where table_name = 'PART_TEST')
 loop
 execute immediate 'alter table part_test modify partition ' || p.partition_name || ' add subpartition ' || p.partition_name || 'AXCS values (''AXCS'')'; 
 end loop;
end;
/