My Oracle Support Banner

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

To view full details, sign in with your My Oracle Support account.

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


In this Document
Goal
Solution
References


This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process and therefore has not been subject to an independent technical review.
My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.