How to move XML LOB segments in a partitioned table to a new tablespace

The examples used in this article is based on Oracle 18c.

When moving a partition with XMLTYPE columns to a new tablespace, the LOB objects that was created by the XMLTYPE objects will follow along with the partition when you move it. Only the LOB partitions based on data types CLOB (and I assume also BLOB) will have to be moved explicitly.

The table looks as follows. Note the column types of CLOB and XMLTYPE:

CREATE TABLE CS_DOCUMENTS
(
  ENTRY_ID                 VARCHAR2(100 CHAR),
  BATCH_ID                 NUMBER(28),
  DOC_ID                   VARCHAR2(100 CHAR),
  DOC_TYPE                 VARCHAR2(100 CHAR),
  DOC_NAME                 VARCHAR2(4000 CHAR),
  STATUS                   VARCHAR2(4000 CHAR),
  PUBLISHED                TIMESTAMP(6),
  CREATED                  TIMESTAMP(6),
  RAW_DATA                 CLOB,
  DOKCOUNT                 INTEGER,
  REVISION                 INTEGER,
  XML_P_HEADER             SYS.XMLTYPE,
  XML_P_HEADER_FORMAT      VARCHAR2(4000 CHAR),
  XML_P_DATA               SYS.XMLTYPE,
  XML_P_DATA_FORMAT        VARCHAR2(4000 CHAR),
  XML_P_EXTENSION          SYS.XMLTYPE,
  XML_P_EXTENSION_FORMAT   VARCHAR2(4000 CHAR)
)
-- CLOB
LOB (RAW_DATA) STORE AS SECUREFILE (
  TABLESPACE  DATA1
)
-- XMLTYPE
XMLTYPE XML_P_HEADER STORE AS SECUREFILE BINARY XML (
  TABLESPACE  DATA1
  )
-- XMLTYPE
XMLTYPE XML_P_DATA STORE AS SECUREFILE BINARY XML (
  TABLESPACE  DATA1
  )
-- XMLTYPE
XMLTYPE XML_P_EXTENSION STORE AS SECUREFILE BINARY XML (
  TABLESPACE  DATA1
)
TABLESPACE MOTTAK_DATA
PARTITION BY RANGE (BATCH_ID)
INTERVAL(1000)
(  
  PARTITION P_INIT VALUES LESS THAN (1001)
    NOCOMPRESS 
    TABLESPACE DATA1
    LOB (RAW_DATA) STORE AS SECUREFILE (
      TABLESPACE  DATA1
    )
 )
;

The tablespace DATA1 is filling up, and there is a need to move some partitions to another tablespace, DATA2.
In this example, I am moving the latest added partitions first, and working my way backwards.

Let's look at most recently added partition:

SELECT /*+ result_cache */ LP.TABLE_NAME,LP.COLUMN_NAME,LP.LOB_NAME,LP.PARTITION_NAME,LP.LOB_PARTITION_NAME,LP.PARTITION_POSITION,LP.TABLESPACE_NAME,ROUND(SUM(S.BYTES)/1024/1024/1024,2) "GB"
 FROM DBA_LOB_PARTITIONS LP join DBA_TAB_PARTITIONS TP
 ON (LP.PARTITION_NAME = TP.PARTITION_NAME) JOIN DBA_SEGMENTS S
    ON (LP.LOB_PARTITION_NAME = S.PARTITION_NAME)
 WHERE  TP.TABLE_NAME='CS_DOCUMENTS'
 AND     TP.PARTITION_NAME = (
             SELECT PARTITION_NAME
            FROM DBA_TAB_PARTITIONS 
            WHERE TABLE_NAME='CS_DOCUMENTS' 
            AND partition_position=(SELECT MAX(PARTITION_POSITION) FROM DBA_TAB_PARTITIONS WHERE TABLE_NAME='CS_DOCUMENTS' AND TABLESPACE_NAME != 'DATA2'
            )
            )
 GROUP BY LP.TABLE_NAME,LP.COLUMN_NAME,LP.LOB_NAME,LP.PARTITION_NAME,LP.LOB_PARTITION_NAME,LP.LOB_INDPART_NAME,LP.PARTITION_POSITION,LP.TABLESPACE_NAME
 ORDER BY PARTITION_NAME DESC;

Output:

TABLE_NAME	COLUMN_NAME	LOB_NAME	PARTITION_NAME	LOB_PARTITION_NAME	PARTITION_POSITION	TABLESPACE_NAME	GB
CS_DOCUMENTS	RAW_DATA	SYS_LOB0044210973C00012$$	SYS_P6533446	SYS_LOB_P6533447	62	DATA1	31,24
CS_DOCUMENTS	SYS_NC00017$	SYS_LOB0044210973C00017$$	SYS_P6533446	SYS_LOB_P6533449	62	DATA1	0,01
CS_DOCUMENTS	SYS_NC00020$	SYS_LOB0044210973C00020$$	SYS_P6533446	SYS_LOB_P6533451	62	DATA1	0,01
CS_DOCUMENTS	SYS_NC00023$	SYS_LOB0044210973C00023$$	SYS_P6533446	SYS_LOB_P6533453	62	DATA1	0,01

The LOB partition SYS_LOB_P6533447 is occupying 31GB of space.

Move the partition to another tablespace. Notice how I specify tablespace for the table partition and the LOB object, but none of the XMLType objects:

Take the opportunity to compress the LOB objects at the same time. Use the ONLINE clause to allow DML against the table during the move-operation:

set timing on
exec DBMS_APPLICATION_INFO.SET_CLIENT_INFO (client_info=>'move_objects');
exec DBMS_APPLICATION_INFO.SET_MODULE(module_name=>'move_objects', action_name=>NULL);
exec DBMS_APPLICATION_INFO.SET_ACTION(action_name=>'move_lobs');
ALTER TABLE CS_DOCUMENTS
 MOVE PARTITION SYS_P6533446
    ROW STORE COMPRESS ADVANCED
    TABLESPACE DATA2
    LOB (ENTRY_RAW) STORE AS SECUREFILE (
      TABLESPACE  DATA2
      COMPRESS    MEDIUM
      )
 ONLINE;

The situation after the move:

SELECT /*+ result_cache */ LP.TABLE_NAME,LP.COLUMN_NAME,LP.LOB_NAME,LP.PARTITION_NAME,LP.LOB_PARTITION_NAME,LP.PARTITION_POSITION,LP.TABLESPACE_NAME,SUM(S.BYTES)/1024/1024/1024 "GB"
 FROM DBA_LOB_PARTITIONS LP join DBA_TAB_PARTITIONS TP
 ON (LP.PARTITION_NAME = TP.PARTITION_NAME) JOIN DBA_SEGMENTS S
    ON (LP.LOB_PARTITION_NAME = S.PARTITION_NAME)
 WHERE  TP.TABLE_NAME='CS_DOCUMENTS'
 AND     TP.PARTITION_NAME = (
             SELECT PARTITION_NAME
            FROM DBA_TAB_PARTITIONS 
            WHERE TABLE_NAME='CS_DOCUMENTS' 
            AND partition_position=(SELECT MIN(PARTITION_POSITION) FROM DBA_TAB_PARTITIONS WHERE TABLE_NAME='CS_DOCUMENTS' AND TABLESPACE_NAME = 'DATA2'
            )
            )
 GROUP BY LP.TABLE_NAME,LP.COLUMN_NAME,LP.LOB_NAME,LP.PARTITION_NAME,LP.LOB_PARTITION_NAME,LP.LOB_INDPART_NAME,LP.PARTITION_POSITION,LP.TABLESPACE_NAME
 ORDER BY PARTITION_NAME DESC;

Output:

TABLE_NAME	COLUMN_NAME	LOB_NAME	PARTITION_NAME	LOB_PARTITION_NAME	PARTITION_POSITION	TABLESPACE_NAME	GB
CS_DOCUMENTS	RAW_DATA	SYS_LOB0044210973C00012$$	SYS_P6533446	SYS_LOB_P6533447	62	DATA2	0,0078125
CS_DOCUMENTS	SYS_NC00017$	SYS_LOB0044210973C00017$$	SYS_P6533446	SYS_LOB_P6533449	62	DATA2	0,0078125
CS_DOCUMENTS	SYS_NC00020$	SYS_LOB0044210973C00020$$	SYS_P6533446	SYS_LOB_P6533451	62	DATA2	0,0078125
CS_DOCUMENTS	SYS_NC00023$	SYS_LOB0044210973C00023$$	SYS_P6533446	SYS_LOB_P6533453	62	DATA2	0,0078125

ALTER TABLE .... MOVE PARTITION statments can be generated on a partition-by-partition basis with:

SELECT 'alter table ' || TABLE_OWNER || '.' || TABLE_NAME || ' move partition ' || PARTITION_NAME || ' ROW STORE COMPRESS ADVANCED TABLESPACE DATA2 lob (' || COLUMN_NAME || ') store as SECUREFILE (tablespace DATA2 COMPRESS MEDIUM) online update indexes;' 
FROM DBA_LOB_PARTITIONS 
WHERE TABLE_OWNER = 'CS'
and table_name='CS_DOCUMENT'
AND PARTITION_POSITION = (SELECT MAX(PARTITION_POSITION) FROM DBA_LOB_PARTITIONS WHERE TABLE_NAME='CS_DOCUMENT'  AND TABLESPACE_NAME != 'DATA2')
AND COLUMN_NAME = (SELECT COLUMN_NAME FROM DBA_TAB_COLUMNS WHERE TABLE_NAME='CS_DOCUMENTS' AND DATA_TYPE LIKE ('%LOB%'));