How to completely move a sub-partitioned table from one tablespace to another, including attributes

1. move the subpartitions physically:

ALTER TABLE lkw.sales MOVE SUBPARTITION P_012013_WEEK1 TABLESPACE SALES_2013_DATA [ UPDATE INDEXES ];

2. alter the default attributes for the *partition* (as opposed to the subpartition which was physically moved in the previous step):

ALTER TABLE lkw.sales MODIFY DEFAULT ATTRIBUTES FOR PARTITION "P_012013" TABLESPACE SALES_2013_DATA;

3. Finally, alter the default attributes for the table:

ALTER TABLE lkw.sales MODIFY DEFAULT ATTRIBUTES TABLESPACE SALES_2013_DATA;


The default attributes for partitions and subpartitions can be found by querying the view DBA_PART_TABLES.

A script that will generate the DDL for you:

accept table_owner prompt 'Table owner: '
accept table_name prompt 'Table name: '
accept new_tabspc prompt 'New tablespace: '
accept old_tabspc prompt 'Old tablespace: '

set lines 300
set heading off
set feedback off
set verify off
set echo off
set pages 0
set trimspool on
spool exec_movesubpart_&&table_owner..&&table_name..sql
select 'alter session set nls_language=''american'';' from dual;
select 'alter session force parallel ddl;' from dual;
select 'set timing on' from dual;
select 'set lines 200' from dual;
select 'set trimspool on' from dual;
select 'spool exec_move_subpart_&&table_owner..&&table_name..log' from dual;
select 'exec DBMS_APPLICATION_INFO.SET_MODULE(module_name=>''Move'', action_name=>''MoveSubPart_&&table_name'');' from dual;
select 'exec DBMS_APPLICATION_INFO.SET_CLIENT_INFO (client_info=>''MoveSubPart_&&table_name'');' from dual;
select 'exec DBMS_SESSION.SET_IDENTIFIER (client_id=>''MoveSubPart_&&table_name''); ' from dual;

SELECT 'alter table ' || ts.TABLE_OWNER || '.' || ts.TABLE_NAME || ' move subpartition ' || ts.subpartition_name ||  ' ONLINE TABLESPACE  &&new_tabspc ROW STORE COMPRESS ADVANCED UPDATE GLOBAL INDEXES;'
FROM dba_tab_subpartitions ts
WHERE ts.table_name      = '&&table_name'
AND   ts.tablespace_name = '&&old_tabspc'
AND   ts.table_owner     = '&&table_owner'
ORDER BY ts.subpartition_name DESC;
select 'prompt alter default attributes for partitions:' from dual;
SELECT 'alter table ' || ts.TABLE_OWNER || '.' || ts.TABLE_NAME || ' MODIFY DEFAULT ATTRIBUTES FOR PARTITION ' || ts.partition_name ||  ' TABLESPACE  &&new_tabspc;'
FROM dba_tab_subpartitions ts
WHERE ts.table_name      = '&&table_name'
AND   ts.tablespace_name = '&&old_tabspc'
AND   ts.table_owner     = '&&table_owner'
ORDER BY ts.subpartition_name DESC;
select 'exit' from dual;
exit

From 12.1 and onwards, this operation can be done online, meaning that it becomes a non-blocking DDL statement, allowing DML against the partition to continue without interuption. See this post for details.