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