Step 1. Build the temp table with same structure as the partitions in the partitioned table. Step 2. Build constraints (FKs for example), indexes on the temp table. Don't worry about the naming. Step 3. Alter table exchange partition with table inluding indexes without validation SQL> create table part_range_temp (c1 number(3), c2 varchar2(2), c3 char(1)) 2 partition by range (c1) 3 subpartition by list (c3) 4 (partition p1 values less than (100) 5 (subpartition p1_d1 values('A'), 6 subpartition p1_d2 values('B') 7 ), 8 partition p2 values less than (200) 9 (subpartition p2_d1 values('A'), 10 subpartition p2_d2 values('B') 11 ), 12 partition p3 values less than (300) 13 (subpartition p3_d1 values('A'), 14 subpartition p3_d2 values('B') 15 ) 16 ); Table created. SQL> drop table part_range_temp; Table dropped. SQL> drop table temp_test; drop table temp_test * ERROR at line 1: ORA-00942: table or view does not exist SQL> drop table part_range_lookup; Table dropped. SQL> SQL> create table part_range_temp (c1 number(3), c2 varchar2(2), c3 char(1)) 2 partition by range (c1) 3 subpartition by list (c3) 4 (partition p1 values less than (100) 5 (subpartition p1_d1 values('A'), 6 subpartition p1_d2 values('B') 7 ), 8 partition p2 values less than (200) 9 (subpartition p2_d1 values('A'), 10 subpartition p2_d2 values('B') 11 ), 12 partition p3 values less than (300) 13 (subpartition p3_d1 values('A'), 14 subpartition p3_d2 values('B') 15 ) 16 ); Table created. SQL> create table part_range_lookup(c3 char(1), c3_name varchar2(10)); Table created. SQL> Alter table part_range_lookup add constraint part_range_lookup_pk 2 primary key(c3); Table altered. SQL> SQL> insert into part_range_lookup values('A','A tiger'); 1 row created. SQL> insert into part_range_lookup values('B','B cat'); 1 row created. SQL> insert into part_range_lookup values('C','C dog'); 1 row created. SQL> SQL> -- Create FK will not create index, create unique constraint will. SQL> create index part_range_temp_idx on part_range_temp (c1, c3) 2 local; Index created. SQL> Alter table part_range_temp add constraint part_range_temp_fk foreign key (c3) 2 references part_range_lookup(c3); Table altered. SQL> create bitmap index PART_RANGE_TEMP_c3_bmp on PART_RANGE_TEMP(c3) local; Index created. SQL> insert into part_range_temp values(50, 'p1', 'A'); 1 row created. SQL> insert into part_range_temp values(50, 'p1', 'B'); 1 row created. SQL> insert into part_range_temp values(150, 'p2', 'A'); 1 row created. SQL> insert into part_range_temp values(150, 'p2', 'B'); 1 row created. SQL> insert into part_range_temp values(250, 'p3', 'A'); 1 row created. SQL> insert into part_range_temp values(250, 'p3', 'B'); 1 row created. SQL> SQL> create table temp_test (c1 number(3), c2 varchar2(2), c3 char(1)) 2 partition by list (c3) 3 (partition p1 values('A'), 4 partition p2 values('B')); Table created. SQL> SQL> alter table temp_test add constraint temp_test_fk foreign key (c3) 2 references part_range_lookup(c3); Table altered. SQL> create index temp_test_idx on temp_test (c1, c3) 2 local; Index created. SQL> create bitmap index p4 on temp_test(c3) local; Index created. SQL> insert into temp_test values(350,'p4','A'); 1 row created. SQL> insert into temp_test values(350,'p4','B'); 1 row created. SQL> alter table part_range_temp add partition p4 values less than (400) 2 (subpartition p4_d1 values('A'), 3 subpartition p4_d2 values('B')); Table altered. SQL> SQL> alter table part_range_temp exchange partition p4 with table temp_test 2 including indexes without validation; Table altered. SQL> select index_name,partition_name,subpartition_name,status from user_ind_subpartitions 2 where index_name='PART_RANGE_TEMP_IDX'; INDEX_NAME PARTITION_NAME ------------------------------ ------------------------------ SUBPARTITION_NAME STATUS ------------------------------ -------- PART_RANGE_TEMP_IDX P1 P1_D1 USABLE PART_RANGE_TEMP_IDX P1 P1_D2 USABLE PART_RANGE_TEMP_IDX P2 P2_D1 USABLE INDEX_NAME PARTITION_NAME ------------------------------ ------------------------------ SUBPARTITION_NAME STATUS ------------------------------ -------- PART_RANGE_TEMP_IDX P2 P2_D2 USABLE PART_RANGE_TEMP_IDX P3 P3_D1 USABLE PART_RANGE_TEMP_IDX P3 P3_D2 USABLE INDEX_NAME PARTITION_NAME ------------------------------ ------------------------------ SUBPARTITION_NAME STATUS ------------------------------ -------- PART_RANGE_TEMP_IDX P4 P4_D1 USABLE PART_RANGE_TEMP_IDX P4 P4_D2 USABLE 8 rows selected. SQL> select index_name,partition_name,subpartition_name,status from user_ind_subpartitions 2 where index_name='PART_RANGE_TEMP_C3_BMP'; INDEX_NAME PARTITION_NAME ------------------------------ ------------------------------ SUBPARTITION_NAME STATUS ------------------------------ -------- PART_RANGE_TEMP_C3_BMP P1 P1_D1 USABLE PART_RANGE_TEMP_C3_BMP P1 P1_D2 USABLE PART_RANGE_TEMP_C3_BMP P2 P2_D1 USABLE INDEX_NAME PARTITION_NAME ------------------------------ ------------------------------ SUBPARTITION_NAME STATUS ------------------------------ -------- PART_RANGE_TEMP_C3_BMP P2 P2_D2 USABLE PART_RANGE_TEMP_C3_BMP P3 P3_D1 USABLE PART_RANGE_TEMP_C3_BMP P3 P3_D2 USABLE INDEX_NAME PARTITION_NAME ------------------------------ ------------------------------ SUBPARTITION_NAME STATUS ------------------------------ -------- PART_RANGE_TEMP_C3_BMP P4 P4_D1 USABLE PART_RANGE_TEMP_C3_BMP P4 P4_D2 USABLE 8 rows selected. SQL> select object_id,data_object_id from user_objects where object_name='TEMP_TEST_IDX'; OBJECT_ID DATA_OBJECT_ID ---------- -------------- 48117 48125 48118 48126 48116 SQL> -- Note that much like table itself, index's object_ids were swapped after exchanging SQL> -- partitions while object_name remain unchanged SQL>