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.
great solution Vegard Kåsa, thanks a lot for sharing. Just one more doubt. After changing all the partitions, how do I make the next partitions already record the data in the lobs tablespace?
ReplyDelete