You can also use dbms_redefinition but I'm not sure it's faster: 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> 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> exec dbms_redefinition.start_redef_table('bing','part_test','part_test2', null, dbms_redefinition.cons_u PL/SQL procedure successfully completed. SQL> SQL> declare 2 3 num_errs pls_integer; 4 5 begin 6 dbms_redefinition.copy_table_dependents('bing', 'part_test','part_test2', 7 dbms_redefinition.cons_orig_params, true, true, true, true, num_errs); 8 end; 9 / PL/SQL procedure successfully completed. SQL> SQL> select object_name, base_table_name, ddl_txt from 2 dba_redefinition_errors; no rows selected SQL> SQL> exec dbms_redefinition.sync_interim_table('bing','part_test','part_test2') PL/SQL procedure successfully completed. SQL> SQL> exec dbms_redefinition.finish_redef_table('bing','part_test','part_test2') PL/SQL procedure successfully completed. SQL> SQL> select count(*) from part_test subpartition(partition1_good); COUNT(*) ---------- 2000 SQL> select count(*) from part_test subpartition(partition1_bad); COUNT(*) ---------- 1334 SQL> select count(*) from part_test subpartition(partition2_good); COUNT(*) ---------- 1334 SQL> select count(*) from part_test subpartition(partition2_bad); COUNT(*) ---------- 2001 SQL> select count(*) from part_test subpartition(partition3_good); COUNT(*) ---------- 1999 SQL> select count(*) from part_test subpartition(partition3_bad); COUNT(*) ---------- 1332 SQL> SQL> drop table part_test2 purge; Table dropped. SQL> David Fitzjarrell ________________________________ From: Zabair Ahmed <roon987@xxxxxxxxxxx> To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>; "mwf@xxxxxxxx" <mwf@xxxxxxxx> Sent: Wednesday, November 28, 2012 12:37 PM Subject: Re: RE: Partitioning Thanks Mark First reaction is -- whhhatt. This seems to be beyond my SQL/partitioning knowledge. But I will continue with my analysis/research on the issue. Yes I do have a test system and I've already created the partitioned here and populated it with sample data. I am now in position to write and test the approach to be taken. The question I have for you is would the approach be any easier if I could hold 2 copies of the table in PROD. And if I had this luxury how would you go about doing it. Thanks for your input so far, much appreciated. Sent from Yahoo! Mail on Android -- //www.freelists.org/webpage/oracle-l -- //www.freelists.org/webpage/oracle-l