How to add Subpartition to all partitions

Posted on

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 ?

Answer :

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;
/

Leave a Reply

Your email address will not be published. Required fields are marked *