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%'));

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

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

During an attempt to move a partition that contained a LOB segment from one tablespace to another, I hit the infamous ORA-14511 "Cannot perform operation on a partitioned object" more than once. Neither is the Oracle documentation very clear in the matter.

Here is how I relocated LOB segments in a partitioned table from tablespace USERS to the correct tablespace DATA1.

The original DDL for the was as follows:

CREATE TABLE SCOTT.MYTABLE
(
  BQID                    VARCHAR2(36 BYTE)     NOT NULL,
  KLM_ID                  VARCHAR2(36 BYTE),
  PANUM                   NUMBER(18),
  IAA                     NUMBER(16),
  OPPGVTYP                VARCHAR2(50 BYTE),
  BSTAT                   VARCHAR2(60 BYTE),
  LEVREF                  VARCHAR2(50 BYTE)     NOT NULL,
  KSYSTEM                 VARCHAR2(255 BYTE),
  ANT_OPPG                NUMBER(18),
  TOTALER                 CLOB,
  OPPR                    TIMESTAMP(6)          NOT NULL,
  OPP_KI                  VARCHAR2(30 BYTE)     NOT NULL,
  SIST_ENDRET             TIMESTAMP(6)          NOT NULL,
  XML_INNHOLD             CLOB,
  OPPSUMMERING            CLOB,
  ERSTATTERREFERANSE      VARCHAR2(150 CHAR),
  LEVERANSETIDSPUNKT      TIMESTAMP(6),
  LEVERANSETYPE           VARCHAR2(150 BYTE),
  TMP_MD5_OPPSUMMERING    VARCHAR2(32 BYTE)
)
LOB (TOTALER) STORE AS (
  TABLESPACE  DATA1
 )
LOB (XML_INNHOLD) STORE AS (
  TABLESPACE  DATA1
 )
LOB (OPPSUMMERING) STORE AS (
  TABLESPACE  DATA1
 )
TABLESPACE DATA1
PARTITION BY LIST (TASKTYPE)
(  
  PARTITION P_MESSAGE VALUES ('EZAP')
    TABLESPACE DATA1
    LOB (TOTALER) STORE AS (
      TABLESPACE  USERS
    )
    LOB (XML_INNHOLD) STORE AS (
      TABLESPACE  USERS
    )
    LOB (OPPSUMMERING) STORE AS (
      TABLESPACE  USERS
    )
    ,  
  PARTITION P_DEFAULT VALUES (DEFAULT)
    TABLESPACE DATA1
    LOB (TOTALER) STORE AS (
      TABLESPACE  USERS
    )
    LOB (XML_INNHOLD) STORE AS (
      TABLESPACE  USERS
    )
    LOB (OPPSUMMERING) STORE AS (
      TABLESPACE  USERS
    )
)
;

A query against DBA_LOB_PARTITIONS shows that they are indeed placed in tablespace USERS:

SELECT TABLE_OWNER,TABLE_NAME,COLUMN_NAME,LOB_NAME,PARTITION_NAME,LOB_PARTITION_NAME,SECUREFILE,TABLESPACE_NAME
FROM DBA_LOB_PARTITIONS 
WHERE TABLE_OWNER = 'SCOTT' 
AND TABLESPACE_NAME = 'USERS'
AND PARTITION_NAME = 'P_AMELDING';

TABLE_OWNER	TABLE_NAME	COLUMN_NAME	LOB_NAME	PARTITION_NAME	LOB_PARTITION_NAME	SECUREFILE	TABLESPACE_NAME
SCOTT	MYTABLE	TOTALER	SYS_LOB0000093789C00010$$	P_AMELDING	SYS_LOB_P701	NO	USERS
SCOTT	MYTABLE	XML_INNHOLD	SYS_LOB0000093789C00020$$	P_AMELDING	SYS_LOB_P703	NO	USERS
SCOTT	MYTABLE	OPPSUMMERING	SYS_LOB0000093789C00021$$	P_AMELDING	SYS_LOB_P705	NO	USERS

The correct syntax to move these LOB segments is:

ALTER TABLE SCOTT.MYTABLE
MOVE PARTITION P_AMELDING
LOB (TOTALER) STORE AS BASICFILE (TABLESPACE DATA1);

ALTER TABLE SCOTT.MYTABLE
MOVE PARTITION P_AMELDING
LOB (OPPSUMMERING) STORE AS BASICFILE (TABLESPACE DATA1);

ALTER TABLE SCOTT.MYTABLE
MOVE PARTITION P_AMELDING
LOB (XML_INNHOLD) STORE AS BASICFILE (TABLESPACE DATA1);

To generate DDL statements:

SELECT 'alter table ' || TABLE_OWNER || '.' || TABLE_NAME || ' move partition ' || partition_name || ' lob (' || COLUMN_NAME || ') store as basicfile (tablespace data1);'
FROM DBA_LOB_PARTITIONS 
WHERE TABLE_OWNER = 'SCOTT' 
AND TABLESPACE_NAME = 'USERS'
AND SECUREFILE='NO';

If the table is also sub-partitioned, you may want to check this post.

How to move a table containing LOB columns to a new tablespace

Find out which tables have LOB objects:

SELECT  TABLE_NAME,COLUMN_NAME,SEGMENT_NAME,TABLESPACE_NAME,INDEX_NAME
FROM    DBA_LOBS 
WHERE   OWNER = 'USR1';

Genrate a "move table" script:

select 'alter table ' || owner || '.' || table_name || ' move lob(' || column_name ||') store as (tablespace LOB_DATA);'
from   dba_tab_columns c
where  c.owner = 'USR1'
and    c.data_type like '%LOB%';

Query returns the following statements:

alter table USR1.LG_LOG move lob(MESSAGE_CONTEXT) store as (tablespace LOB_DATA);
alter table USR1.TFW_TEST_RESULT move lob(SQL_NUM_MATCHED_ERROR_ROWS) store as (tablespace LOB_DATA);

Note:
The LOB index is an internal structure that is strongly associated with LOB storage.
This implies that a user may not drop the LOB index or rebuild it.