This discussion is archived
1 Reply Latest reply: Aug 23, 2013 8:40 AM by 283a4243-0791-426b-82a1-0534614755f1 RSS

To increase the XMLtype column data length

283a4243-0791-426b-82a1-0534614755f1 Newbie
Currently Being Moderated

Hi ,

 

I have created a Table with an XMLtype column , when I am checking the data length it is showing only 2000.

 

How can I increase the length.

 

DBMS_METADATA.GET_DDL('TABLE','CC_EVENT_MESSAGES','UNS_STAGING_DEV5')

--------------------------------------------------------------------------------

  CREATE TABLE "UNS_STAGING_DEV5"."CC_EVENT_MESSAGES"

   (    "SRC_SYSTEM_CD" VARCHAR2(10) NOT NULL ENABLE,

        "SRC_SEQUENCE_NUM" NUMBER NOT NULL ENABLE,

        "SRC_EVENT_NAME" VARCHAR2(255) NOT NULL ENABLE,

        "SRC_EVENT_XSD_NAME" VARCHAR2(255) NOT NULL ENABLE,

        "SRC_EVENT_DATE" DATE NOT NULL ENABLE,

        "INSERT_TS" TIMESTAMP (6) NOT NULL ENABLE,

        "PROCESSED_TS" TIMESTAMP (6),

        "STAGING_NUM" NUMBER NOT NULL ENABLE,

        "MESSAGE_STATE" VARCHAR2(10) NOT NULL ENABLE,

        "MESSAGE_FRAME" "SYS"."XMLTYPE"  NOT NULL ENABLE

   ) SEGMENT CREATION IMMEDIATE

  PCTFREE 0 PCTUSED 0 INITRANS 1 MAXTRANS 255

NOCOMPRESS LOGGING

  STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1

  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

  TABLESPACE "UNS_STAGING_DEV5"

XMLTYPE COLUMN "MESSAGE_FRAME" STORE AS BASICFILE CLOB (

  TABLESPACE "UNS_STAGING_DEV5" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION

  CACHE

  STORAGE(INITIAL 65536 NEXT 65536 MINEXTENTS 1 MAXEXTENTS 2147483645

  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1

  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT))

   CACHE

 

 

SQL> select OWNER,TABLE_NAME,column_name,data_type,data_length from dba_tab_columns where DATA_TYPE like '%XMLTYPE' and owner not in ('SYS','SYSTEM','XDB');

 

 

OWNER                TABLE_NAME           COLUMN_NAME     DATA_TYPE  DATA_LENGTH

--------------------         --------------------            ---------------                ----------               -----------

UNS_STAGING_DEV5     CC_EVENT_MESSAGES    MESSAGE_FRAME   XMLTYPE           2000

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points