How to move a partition online from Oracle 12.1 and onwards

From Oracle version 12.1 and ownwards, moving partitions and subpartitions becomes non-blocking DDL operations. DML against the table can continue while the subobject is being moved. Oracle guarantees that global indexes are maintained during the move operation, so you no longer have to specify the "UPDATE INDEXES ONLINE"-clause.

Remember that skipping this clause will trigger Asynchronous Global Index Maintenance during the nightly maintenance window.

To move a subpartition to a new tablespace:

ALTER TABLE MYUSER.MYTABLE 
MOVE SUBPARTITION P_201312 ONLINE
TABLESPACE MYTABSPC;

To compress the subpartition data at the same time, use

ALTER TABLE MYUSER.MYTABLE 
MOVE SUBPARTITION P_201312_P_OPLSUM ONLINE 
TABLESPACE MYTABSPC 
ROW STORE COMPRESS ADVANCED;

To move a partition to a new tablespace using a DOP of 4 while updating any global indexes defined on the table:

ALTER TABLE MYUSER.MYTABLE
MOVE PARTITION P_2018_05 ONLINE
TABLESPACE NEWTABLESPACE
PARALLEL 4;

To compress the partition data at the same time, use

ALTER TABLE MYUSER.MYTABLE
MOVE PARTITION P_2018_06 ONLINE
TABLESPACE NEWTABLESPACE
ROW STORE COMPRESS ADVANCED
PARALLEL 4;

Remember to change the properties for the partition and the table, too:

ALTER TABLE MYUSER.MYTABLE
MODIFY DEFAULT ATTRIBUTES FOR PARTITION P201312
TABLESPACE MYTABSPC;

ALTER TABLE MYUSER.MYTABLE 
MODIFY DEFAULT ATTRIBUTES 
TABLESPACE MYTABSPC;

A simple query to find how much space the partitions are using would be:

select partition_name,tablespace_name, round(sum(bytes)/1024/1024/1024,1) "GB"
from dba_segments
where segment_name='MYTABLE'
group by partition_name,tablespace_name
order by 1;

Example output:

PARTITION_NAME	TABLESPACE_NAME	GB
SYS_SUBP102302	DATA2	8,9
SYS_SUBP102303	DATA2	2
SYS_SUBP102304	DATA2	0
SYS_SUBP120071	DATA2	7,9
SYS_SUBP120072	DATA	1,8
SYS_SUBP120073	DATA2	0
SYS_SUBP12401	DATA	129,5

Source: Oracle Documentation, New Features list, Version 12.1.0.1. See section "2.2.3.4 ONLINE Move Partition"

How to generate scripts to move partitions to a new tablespace while updating the indexes

Probably many ways of doing this, but here is how I generated scripts to move table partitions from one tablespace to another:

-- Find table partitions with GLOBAL indexes:
SELECT 'ALTER TABLE ' || TABLE_OWNER || '.' || TABLE_NAME || ' MOVE PARTITION "' || PARTITION_NAME || '" TABLESPACE DATA2 NOCOMPRESS UPDATE GLOBAL INDEXES;'
FROM    dba_tab_partitions
WHERE   table_owner         = 'USER1'
AND     tablespace_name     = 'DATA1'
AND     subpartition_count  = 0
AND     table_name IN (
        SELECT  distinct table_name   
        FROM    dba_part_indexes
        WHERE   owner = 'USER1'
        AND     locality = 'GLOBAL'
)
ORDER BY table_name DESC;

-- Find table partitions with LOCAL indexes:
SELECT 'ALTER TABLE ' || TABLE_OWNER || '.' || TABLE_NAME || ' MOVE PARTITION "' || PARTITION_NAME || '" TABLESPACE DATA2 NOCOMPRESS UPDATE INDEXES;'
FROM    dba_tab_partitions
WHERE   table_owner         = 'USER1'
AND     tablespace_name     = 'DATA1'
AND     subpartition_count  = 0
AND     table_name IN (
        SELECT  distinct table_name   
        FROM    dba_part_indexes
        WHERE   owner = 'USER1'
        AND     locality = 'LOCAL'
)
ORDER BY table_name DESC;


-- Find table partitions with no index at all
SELECT 'ALTER TABLE ' || TABLE_OWNER || '.' || TABLE_NAME || ' MOVE PARTITION "' || PARTITION_NAME || '" TABLESPACE DATA2 NOCOMPRESS;'
FROM    dba_tab_partitions
WHERE   table_owner         = 'USER1'
AND     tablespace_name     = 'DATA1'
AND     subpartition_count  = 0
AND     table_name NOT IN (
        SELECT  table_name   
        FROM    dba_part_indexes
        WHERE   owner = 'USER1'
)
ORDER BY table_name DESC;