Monday, December 29, 2014

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.

1 comment:

  1. 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