1

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 ?

2
  • @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 Mar 23 '16 at 8:55
  • 1
    @JSapkota That does not affect existing partitions. Mar 23 '16 at 9:49
3

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.