ALTER TABLE MOVE does not move all underlying objects of XDB table (Doc ID 1509227.1)

Last updated on FEBRUARY 08, 2017

Applies to:

Oracle Server - Enterprise Edition - Version 10.1.0.2 to 10.2.0.3 [Release 10.1 to 10.2]
Information in this document applies to any platform.

Symptoms

 ALTER TABLE MOVE does not move all underlying objects of XDB table

CREATE TABLE TABLE_1
(USER_ID NUMBER NOT NULL
,APPLICATION_ID NUMBER NOT NULL
,CONSTRAINT CF_WORKAREA_PK PRIMARY KEY (USER_ID ,APPLICATION_ID)
,WORKAREA_DEFINITION XMLTYPE
)
ORGANIZATION INDEX
TABLESPACE TS_1
/

When trying "ALTER TABLE TABLE_1 MOVE LOB(SYS_NC00004$) STORE AS (TABLESPACE TS_2);"
or
"ALTER TABLE TABLE_1 MOVE TABLESPACE TS_2 LOB (SYS_NC00004$) STORE AS (TABLESPACE TS_2);"
get error "ORA-00932: inconsistent datatypes: expected CLOB got -"
(SYS_NC00004$ taken from USER_LOBS)

When trying "ALTER TABLE TABLE_1 MOVE TABLESPACE TS_2 LOB (SYS_NC00004$.XMLTYPE) STORE AS (TABLESPACE TS_2);"
or
"ALTER TABLE TABLE_1 MOVE TABLESPACE TS_2 LOB (WORKAREA_DEFINITION.XMLTYPE) STORE AS (TABLESPACE TS_2);"
get error "ORA-02337: not an object type column"

 

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms