How to alter the parameters of CLOB associated with a XMLTYPE column (Doc ID 1592721.1)

Last updated on APRIL 12, 2022

Applies to:

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

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.