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.
