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
