What you want to do is still possible: SQL> create table part_test( 2 dataset_id number, 3 status_name varchar2(20), 4 status varchar2(12), 5 stuff varchar2(100) 6 ) 7 partition by list(dataset_id) 8 (partition partition1 values (1,2,3,4,5), 9 partition partition2 values (6,7,8,9,10), 10 partition partition3 values (11,12,13,14,15)) 11 / Table created. SQL> SQL> begin 2 for i in 1..10000 loop 3 insert into part_test 4 values(mod(i,15)+1, 'USABLE','VALID','When in the course of human events'); 5 end loop; 6 7 commit; 8 9 end; 10 / PL/SQL procedure successfully completed. SQL> SQL> update part_test 2 set status_name = 'UNUSABLE', status = 'INVALID' 3 where mod(dataset_id, 2) = 0; 4667 rows updated. SQL> SQL> commit; Commit complete. SQL> SQL> select count(*) from part_test partition(partition1); COUNT(*) ---------- 3334 SQL> select count(*) from part_test partition(partition2); COUNT(*) ---------- 3335 SQL> select count(*) from part_test partition(partition3); COUNT(*) ---------- 3331 SQL> SQL> create table part_test2( 2 dataset_id number, 3 status_name varchar2(20), 4 status varchar2(12), 5 stuff varchar2(100) 6 ) 7 partition by list(dataset_id) 8 subpartition by list(status_name) 9 subpartition template 10 (subpartition good values ('USABLE','GOOD'), 11 subpartition bad values ('UNUSABLE','BAD')) 12 (partition partition1 values (1,2,3,4,5), 13 partition partition2 values (6,7,8,9,10), 14 partition partition3 values (11,12,13,14,15)) 15 / Table created. SQL> SQL> insert into part_test2 select * from part_test; 10000 rows created. SQL> SQL> commit; Commit complete. SQL> SQL> alter table part_test2 modify partition partition1 add subpartition partition1_ugly values ('NONUSABLE','UGLY'); Table altered. SQL> alter table part_test2 modify partition partition2 add subpartition partition2_ugly values ('NONUSABLE','UGLY'); Table altered. SQL> alter table part_test2 modify partition partition3 add subpartition partition3_ugly values ('NONUSABLE','UGLY'); Table altered. SQL> SQL> select count(*) from part_test2 partition(partition1); COUNT(*) ---------- 3334 SQL> select count(*) from part_test2 partition(partition1); COUNT(*) ---------- 3334 SQL> select count(*) from part_test2 partition(partition2); COUNT(*) ---------- 3335 SQL> select count(*) from part_test2 partition(partition2); COUNT(*) ---------- 3335 SQL> select count(*) from part_test2 partition(partition3); COUNT(*) ---------- 3331 SQL> select count(*) from part_test2 partition(partition3); COUNT(*) ---------- 3331 SQL> SQL> select count(*) from part_test2 subpartition(partition1_good); COUNT(*) ---------- 2000 SQL> select count(*) from part_test2 subpartition(partition1_bad); COUNT(*) ---------- 1334 SQL> select count(*) from part_test2 subpartition(partition1_ugly); COUNT(*) ---------- 0 SQL> select count(*) from part_test2 subpartition(partition2_good); COUNT(*) ---------- 1334 SQL> select count(*) from part_test2 subpartition(partition2_bad); COUNT(*) ---------- 2001 SQL> select count(*) from part_test2 subpartition(partition2_ugly); COUNT(*) ---------- 0 SQL> select count(*) from part_test2 subpartition(partition3_good); COUNT(*) ---------- 1999 SQL> select count(*) from part_test2 subpartition(partition3_bad); COUNT(*) ---------- 1332 SQL> select count(*) from part_test2 subpartition(partition3_ugly); COUNT(*) ---------- 0 SQL> David Fitzjarrell ________________________________ From: Zabair Ahmed <roon987@xxxxxxxxxxx> To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>; "oratune@xxxxxxxxx" <oratune@xxxxxxxxx> Sent: Thursday, November 29, 2012 9:20 AM Subject: Re: RE: Partitioning I don't think I explained myself very clearly David. What I hoped for was say that we have created the new partitioned table (PART TEST2) with the subpartition template values of USABLE and UNUSABLE for status-name. Everything is working fine but now the requirement has changed and status-name will have an additional value of say NONUSABLE. How could this be handled. Sent from Yahoo! Mail on Android -- //www.freelists.org/webpage/oracle-l