Here is a demo of moving a partition of the BALANCES table in the BALANCES schema to a separate Schema (ARCH), Tablespace (ARCHIVAL) and Table (BALANCES_ARCH).
SQL> connect hemant/hemant Connected. SQL> -- drop users SQL> drop user BALANCES cascade; User dropped. SQL> drop user ARCH cascade; User dropped. SQL> -- set target file dest SQL> alter system set db_create_file_dest='/tmp/'; System altered. SQL> SQL> -- create tablespaces SQL> drop tablespace balances including contents and datafiles; Tablespace dropped. SQL> create tablespace balances ; Tablespace created. SQL> SQL> drop tablespace archival including contents and datafiles; Tablespace dropped. SQL> create tablespace archival ; Tablespace created. SQL> SQL> -- create users SQL> drop user BALANCES cascade; drop user BALANCES cascade * ERROR at line 1: ORA-01918: user 'BALANCES' does not exist SQL> create user BALANCES identified by BALANCES default tablespace balances; User created. SQL> grant create session to BALANCES; Grant succeeded. SQL> alter user BALANCES quota unlimited on balances; User altered. SQL> alter user BALANCES quota unlimited on archival; User altered. SQL> grant create table to BALANCES; Grant succeeded. SQL> SQL> drop user ARCH cascade; drop user ARCH cascade * ERROR at line 1: ORA-01918: user 'ARCH' does not exist SQL> create user ARCH identified by ARCH default tablespace archival; User created. SQL> grant create session to ARCH; Grant succeeded. SQL> alter user ARCH quota unlimited on archival; User altered. SQL> alter user ARCH quota unlimited on balances; User altered. SQL> grant create table to ARCH; Grant succeeded. SQL> SQL> -- rem create source table SQL> connect BALANCES/BALANCES Connected. SQL> create table BALANCES 2 (branch_code varchar2(5), 3 account_number number, 4 fiscal_year number, 5 accounting_period number, 6 balance number) 7 partition by range (branch_code, fiscal_year, accounting_period) 8 (partition ABC_2012_1 values less than ('ABC',2012,2), 9 partition ABC_2012_2 values less than ('ABC',2012,3), 10 partition ABC_2012_3 values less than ('ABC',2012,4), 11 partition ABC_2012_4 values less than ('ABC',2012,5), 12 partition ABC_2012_5 values less than ('ABC',2012,6), 13 partition ABC_2012_6 values less than ('ABC',2012,7), 14 partition XYZ_2012_1 values less than ('XYZ',2012,2), 15 partition XYZ_2012_2 values less than ('XYZ',2012,3), 16 partition XYZ_2012_3 values less than ('XYZ',2012,4), 17 partition XYZ_2012_4 values less than ('XYZ',2012,5), 18 partition XYZ_2012_5 values less than ('XYZ',2012,6), 19 partition XYZ_2012_6 values less than ('XYZ',2012,7) 20 ) 21 tablespace balances; Table created. SQL> create index BALANCES_NDX on 2 BALANCES(BRANCH_CODE, ACCOUNT_NUMBER, FISCAL_YEAR, ACCOUNTING_PERIOD) 3 LOCAL; Index created. SQL> SQL> SQL> SQL> --- rem create target archival table SQL> connect ARCH/ARCH Connected. SQL> create table BALANCES_ARCH 2 (branch_code varchar2(5), 3 account_number number, 4 fiscal_year number, 5 accounting_period number, 6 balance number) 7 partition by range (branch_code, fiscal_year, accounting_period) 8 (partition ABC_2012_1 values less than ('ABC',2012,2), 9 partition ABC_2012_2 values less than ('ABC',2012,3), 10 partition ABC_2012_3 values less than ('ABC',2012,4), 11 partition ABC_2012_4 values less than ('ABC',2012,5), 12 partition ABC_2012_5 values less than ('ABC',2012,6), 13 partition ABC_2012_6 values less than ('ABC',2012,7), 14 partition XYZ_2012_1 values less than ('XYZ',2012,2), 15 partition XYZ_2012_2 values less than ('XYZ',2012,3), 16 partition XYZ_2012_3 values less than ('XYZ',2012,4), 17 partition XYZ_2012_4 values less than ('XYZ',2012,5), 18 partition XYZ_2012_5 values less than ('XYZ',2012,6), 19 partition XYZ_2012_6 values less than ('XYZ',2012,7) 20 ) 21 tablespace archival; Table created. SQL> create index BALANCES_ARCH_NDX on 2 BALANCES_ARCH(BRANCH_CODE, ACCOUNT_NUMBER, FISCAL_YEAR, ACCOUNTING_PERIOD) 3 LOCAL; Index created. SQL> SQL> SQL> -- insert data into source SQL> connect BALANCES/BALANCES Connected. SQL> insert into balances 2 select decode(mod(rownum,2),0,'ABC',1,'XYZ'),rownum,2012,1,1000 3 from dual 4 connect by level < 10 5 / 9 rows created. SQL> insert into balances 2 select decode(mod(rownum,2),0,'ABC',1,'XYZ'),rownum,2012,2,2000 3 from dual 4 connect by level < 10 5 / 9 rows created. SQL> insert into balances 2 select decode(mod(rownum,2),0,'ABC',1,'XYZ'),rownum,2012,3,3000 3 from dual 4 connect by level < 10 5 / 9 rows created. SQL> SQL> commit; Commit complete. SQL> SQL> REM REM REM SQL> -- select branch_code, account_number, fiscal_year, accounting_period, count(*) SQL> -- from balances SQL> -- group by SQL> -- branch_code, account_number, fiscal_year, accounting_period SQL> -- order by 1,2,3,4 SQL> -- / SQL> REM REM REM SQL> SQL> -- create intermediate table with index SQL> connect BALANCES/BALANCES Connected. SQL> create table balances_exch_tbl tablespace balances as select * from balances where 1=2; Table created. SQL> create index balances_exch_tbl_ndx on balances_exch_tbl(branch_code, account_number, fiscal_year, accounting_period) 2 tablespace balances; Index created. SQL> grant select, alter on balances_exch_tbl to ARCH; Grant succeeded. SQL> SQL> -- verify row counts in source and archival partitions SQL> connect BALANCES/BALANCES Connected. SQL> select count(*) from BALANCES partition (ABC_2012_1); COUNT(*) ---------- 4 SQL> connect ARCH/ARCH Connected. SQL> select count(*) from BALANCES_ARCH partition (ABC_2012_1); COUNT(*) ---------- 0 SQL> SQL> -- exchange for account_period 1 for ABC SQL> connect BALANCES/BALANCES Connected. SQL> alter table BALANCES exchange partition ABC_2012_1 with table balances_exch_tbl including indexes without validation; Table altered. SQL> connect ARCH/ARCH Connected. SQL> alter table BALANCES_ARCH exchange partition ABC_2012_1 with table balances.balances_exch_tbl including indexes without validation; Table altered. SQL> alter table BALANCES_ARCH move partition ABC_2012_1 tablespace archival; Table altered. SQL> connect BALANCES/BALANCES Connected. SQL> alter table balances_exch_tbl move tablespace balances; Table altered. SQL> alter index balances_exch_tbl_ndx rebuild tablespace balances; Index altered. SQL> SQL> SQL> -- verify row counts in source and archival partitions SQL> connect BALANCES/BALANCES Connected. SQL> select count(*) from BALANCES partition (ABC_2012_1); COUNT(*) ---------- 0 SQL> connect ARCH/ARCH Connected. SQL> select count(*) from BALANCES_ARCH partition (ABC_2012_1); COUNT(*) ---------- 4 SQL> SQL> -- query for segment tablespaces SQL> connect hemant/hemant Connected. SQL> select owner, segment_name, segment_type, partition_name, tablespace_name 2 from dba_segments 3 where segment_name like 'BALANC%' 4 order by 1,2,3,4 5 / OWNER SEGMENT_NAME SEGMENT_TYPE PARTITION_NAME TABLESPACE_NAME ---------- --------------------- ---------------- --------------------- --------------------- ARCH BALANCES_ARCH TABLE PARTITION ABC_2012_1 ARCHIVAL ARCH BALANCES_ARCH TABLE PARTITION ABC_2012_2 ARCHIVAL ARCH BALANCES_ARCH TABLE PARTITION ABC_2012_3 ARCHIVAL ARCH BALANCES_ARCH TABLE PARTITION ABC_2012_4 ARCHIVAL ARCH BALANCES_ARCH TABLE PARTITION ABC_2012_5 ARCHIVAL ARCH BALANCES_ARCH TABLE PARTITION ABC_2012_6 ARCHIVAL ARCH BALANCES_ARCH TABLE PARTITION XYZ_2012_1 ARCHIVAL ARCH BALANCES_ARCH TABLE PARTITION XYZ_2012_2 ARCHIVAL ARCH BALANCES_ARCH TABLE PARTITION XYZ_2012_3 ARCHIVAL ARCH BALANCES_ARCH TABLE PARTITION XYZ_2012_4 ARCHIVAL ARCH BALANCES_ARCH TABLE PARTITION XYZ_2012_5 ARCHIVAL ARCH BALANCES_ARCH TABLE PARTITION XYZ_2012_6 ARCHIVAL ARCH BALANCES_ARCH_NDX INDEX PARTITION ABC_2012_2 ARCHIVAL ARCH BALANCES_ARCH_NDX INDEX PARTITION ABC_2012_3 ARCHIVAL ARCH BALANCES_ARCH_NDX INDEX PARTITION ABC_2012_4 ARCHIVAL ARCH BALANCES_ARCH_NDX INDEX PARTITION ABC_2012_5 ARCHIVAL ARCH BALANCES_ARCH_NDX INDEX PARTITION ABC_2012_6 ARCHIVAL ARCH BALANCES_ARCH_NDX INDEX PARTITION XYZ_2012_1 ARCHIVAL ARCH BALANCES_ARCH_NDX INDEX PARTITION XYZ_2012_2 ARCHIVAL ARCH BALANCES_ARCH_NDX INDEX PARTITION XYZ_2012_3 ARCHIVAL ARCH BALANCES_ARCH_NDX INDEX PARTITION XYZ_2012_4 ARCHIVAL ARCH BALANCES_ARCH_NDX INDEX PARTITION XYZ_2012_5 ARCHIVAL ARCH BALANCES_ARCH_NDX INDEX PARTITION XYZ_2012_6 ARCHIVAL BALANCES BALANCES TABLE PARTITION ABC_2012_1 BALANCES BALANCES BALANCES TABLE PARTITION ABC_2012_2 BALANCES BALANCES BALANCES TABLE PARTITION ABC_2012_3 BALANCES BALANCES BALANCES TABLE PARTITION ABC_2012_4 BALANCES BALANCES BALANCES TABLE PARTITION ABC_2012_5 BALANCES BALANCES BALANCES TABLE PARTITION ABC_2012_6 BALANCES BALANCES BALANCES TABLE PARTITION XYZ_2012_1 BALANCES BALANCES BALANCES TABLE PARTITION XYZ_2012_2 BALANCES BALANCES BALANCES TABLE PARTITION XYZ_2012_3 BALANCES BALANCES BALANCES TABLE PARTITION XYZ_2012_4 BALANCES BALANCES BALANCES TABLE PARTITION XYZ_2012_5 BALANCES BALANCES BALANCES TABLE PARTITION XYZ_2012_6 BALANCES BALANCES BALANCES_EXCH_TBL TABLE BALANCES BALANCES BALANCES_EXCH_TBL_NDX INDEX BALANCES BALANCES BALANCES_NDX INDEX PARTITION ABC_2012_1 BALANCES BALANCES BALANCES_NDX INDEX PARTITION ABC_2012_2 BALANCES BALANCES BALANCES_NDX INDEX PARTITION ABC_2012_3 BALANCES BALANCES BALANCES_NDX INDEX PARTITION ABC_2012_4 BALANCES BALANCES BALANCES_NDX INDEX PARTITION ABC_2012_5 BALANCES BALANCES BALANCES_NDX INDEX PARTITION ABC_2012_6 BALANCES BALANCES BALANCES_NDX INDEX PARTITION XYZ_2012_1 BALANCES BALANCES BALANCES_NDX INDEX PARTITION XYZ_2012_2 BALANCES BALANCES BALANCES_NDX INDEX PARTITION XYZ_2012_3 BALANCES BALANCES BALANCES_NDX INDEX PARTITION XYZ_2012_4 BALANCES BALANCES BALANCES_NDX INDEX PARTITION XYZ_2012_5 BALANCES BALANCES BALANCES_NDX INDEX PARTITION XYZ_2012_6 BALANCES 49 rows selected. SQL>The account HEMANT is the DBA account. BALANCES owns the Source BALANCES table in the BALANCES Tablespace. ARCH owns the the Archival BALANCES_ARCH table in the ARCHIVAL Tablespace.