How to alter the parameters of CLOB associated with a XMLTYPE column

(Doc ID 1592721.1)

Last updated on OCTOBER 16, 2013

Applies to:

Oracle Database - Enterprise Edition - Version 11.2.0.3 and later
Information in this document applies to any platform.

Goal

How to alter the LOB parameters associated with a XMLTYPE column stored in a Dictionary managed tablespace.

Table definition:

CREATE TABLE "XML_TAB"
 (    "ARCHIVEID" NUMBER,
      "TEST_XML" "XMLTYPE",
      "TESTING_XML" "XMLTYPE",
 ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 262144 NEXT 262144 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT)
XMLTYPE COLUMN "TEST_XML" STORE AS CLOB (
TABLESPACE "MYTBS" ENABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 10
NOCACHE LOGGING
STORAGE(INITIAL 262144 NEXT 262144 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT))
XMLTYPE COLUMN "TESTING_XML" STORE AS CLOB (
TABLESPACE "MYTBS" ENABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 10
NOCACHE LOGGING
STORAGE(INITIAL 262144 NEXT 262144 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT))

Trying to modify the XMLTYPE column fails with:
 

SQL>  alter table XML_TAB modify lob (TEST_XML) (STORAGE (MAXEXTENTS UNLIMITED));
alter table XML_TAB modify lob (TEST_XML) (STORAGE (MAXEXTENTS UNLIMITED))
                                              *
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 205
ORA-00904: "TEST_XML": invalid identifier

 

Solution

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