How to move LOB segments in a sub-partitioned table to a new tablespace

Find the sub-partitions that you would like to relocate:

SELECT TABLE_OWNER,TABLE_NAME,COLUMN_NAME,LOB_NAME,SUBPARTITION_NAME,LOB_PARTITION_NAME,SECUREFILE,TABLESPACE_NAME
FROM DBA_LOB_SUBPARTITIONS 
WHERE TABLE_OWNER = 'SCOTT'
AND TABLESPACE_NAME = 'USERS';

TABLE_OWNER	TABLE_NAME	COLUMN_NAME	LOB_NAME	SUBPARTITION_NAME	LOB_PARTITION_NAME	SECUREFILE	TABLESPACE_NAME
SCOTT	TMP_POSK_LEVERANSER	TOTALER	SYS_LOB0000093760C00010$$	P_2013_P_AMELDING	SYS_LOB_P441	NO	USERS
SCOTT	TMP_POSK_LEVERANSER	TOTALER	SYS_LOB0000093760C00010$$	P_2013_P_DEFAULT	SYS_LOB_P441	NO	USERS
SCOTT	TMP_POSK_LEVERANSER	XML_INNHOLD	SYS_LOB0000093760C00020$$	P_2013_P_AMELDING	SYS_LOB_P447	NO	USERS
SCOTT	TMP_POSK_LEVERANSER	XML_INNHOLD	SYS_LOB0000093760C00020$$	P_2013_P_DEFAULT	SYS_LOB_P447	NO	USERS
SCOTT	TMP_POSK_LEVERANSER	OPPSUMMERING	SYS_LOB0000093760C00021$$	P_2013_P_AMELDING	SYS_LOB_P453	NO	USERS
SCOTT	TMP_POSK_LEVERANSER	OPPSUMMERING	SYS_LOB0000093760C00021$$	P_2013_P_DEFAULT	SYS_LOB_P453	NO	USERS

See this post for a more advanced variant that will also show the size of each LOB subpartition

Some examples of correct syntax to move such LOB segments are given below:

-- move a basicfile LOB
alter table SCOTT.TMP_POSK_LEVERANSER move subpartition P_2013_P_AMELDING lob (TOTALER) store as basicfile (tablespace DATA1);

-- move a securefile LOB. Allow for DML and make sure global indexes are not rendered unusable
alter table SCOTT.TMP_POSK_LEVERANSER 
move subpartition P_2013_P_DEFAULT 
lob (OPPSUMMERING) store as securefile (tablespace DATA1) ONLINE UPDATE GLOBAL INDEXES;

-- move a securefile LOB and compress it
alter table SCOTT.TMP_POSK_LEVERANSER 
move subpartition P_2013_P_MELDING 
lob (PDFATTACHMENT) store as securefile (tablespace LOB_DATA COMPRESS MEDIUM);'

Generate the DDLs:

SELECT 'alter table ' || TABLE_OWNER || '.' || TABLE_NAME || ' move subpartition ' || SUBPARTITION_NAME || ' lob (' || COLUMN_NAME || ') store as securefile|basicfile(tablespace DATA1 compress LOW|MEDIUM|HIGH) ONLINE UPDATE GLOBAL INDEXES;' 
FROM DBA_LOB_SUBPARTITIONS 
WHERE TABLE_OWNER = 'SCOTT'
AND TABLESPACE_NAME = 'USERS'
-- to limit your script to certain subpartitions only, uncomment the line below
-- AND SUBPARTITION_NAME='MY_SUBPART_NAME'
;

I have used this script successfully in the past, which generates a log file and sets MODULE and ACTION etc in its session:

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_move_lob_&&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_lob_&&table_owner..&&table_name..log' from dual;
select 'exec DBMS_APPLICATION_INFO.SET_MODULE(module_name=>''Move'', action_name=>''MoveLOB_&&table_name'');' from dual;
select 'exec DBMS_APPLICATION_INFO.SET_CLIENT_INFO (client_info=>''MoveLOB_&&table_name'');' from dual;
select 'exec DBMS_SESSION.SET_IDENTIFIER (client_id=>''MoveLOB_&&table_name''); ' from dual;


SELECT 'alter table ' || ts.TABLE_OWNER || '.' || ts.TABLE_NAME || ' move subpartition ' || lsp.subpartition_name || ' lob (' || lsp.column_name || ') store as securefile (tablespace &&new_tabspc COMPRESS MEDIUM) ONLINE UPDATE GLOBAL INDEXES;'
FROM dba_segments s JOIN dba_lob_subpartitions lsp
ON (s.partition_name = lsp.lob_subpartition_name)
   JOIN DBA_TAB_SUBPARTITIONS ts
        ON (TS.SUBPARTITION_NAME = lsp.SUBPARTITION_NAME)
WHERE lsp.table_name    = '&&table_name'
AND   ts.table_name     = '&&table_name'
-- To limit the output to a specific tablespace, uncomment the line below
--AND   s.tablespace_name = '&&old_tabspc'
AND   ts.table_owner    = '&&table_owner'
-- To limit output to specific table subpartitions only, uncomment the following row
--AND   lsp.subpartition_name like 'SYS_SUBP186786%'
AND s.segment_name IN ( SELECT lpt.lob_name
                        FROM dba_lob_partitions lpt
                        WHERE lpt.table_name IN ( '&&table_name' ) )
GROUP BY ts.table_owner,ts.table_name,s.SEGMENT_NAME,s.SEGMENT_TYPE,s.SEGMENT_SUBTYPE,s.PARTITION_NAME,lsp.COMPRESSION,lsp.subpartition_name, lsp.column_name,ts.num_rows,lsp.tablespace_name
-- To limit output to lob subpartitions with a specific size, uncomment the restriction above and change the operator to suit your needs ( <, >, =, =>, <= or use BETWEEN x AND y)
--HAVING ROUND(sum(s.bytes)/1024/1024/1024) between 1 and 10
ORDER BY lsp.subpartition_name DESC;

select 'exit' from dual;
exit