Monday, May 07, 2007

Oracle change xmltype column lob tablespace

One friend asked me this question today: How do you chnage xmltype column lob tablespace?

  1. xmltype is an object type, it contains XMLDATA which is a CLOB object, you can treat "column.XMLDATA" as a clob object. please refer to here.
  2. How to move a lob from one tablespace to another? The answer is to use 'alter table move lob(...) ...'; please refer to here.
To put everything together, here is how you can setup and test it:
  1. Create a test table with xmltype column:
    • CREATE TABLE xwarehouses (
      warehouse_id NUMBER,
      warehouse_spec XMLTYPE)
      XMLTYPE warehouse_spec STORE AS CLOB
      (TABLESPACE catusedt
      STORAGE (INITIAL 6144 NEXT 6144)
      CHUNK 4000
      NOCACHE LOGGING);
  2. Alter the table to change the LOB tablespace:
    • alter table xwarehouses
      move LOB(warehouse_spec.XMLDATA)
      STORE AS xwarehouses_seg
      (TABLESPACE catusedpht
      STORAGE (INITIAL 6144 NEXT 6144)
      CHUNK 4000
      NOCACHE LOGGING);

No comments: