Move LOBSEGMENT to different tablespace

When we created table by using "XMLTYPE" datatype...

create table tmp01 (id number, xml_data XMLTYPE) tablespace users;

that made us find LOBSEGMENT Segment Type.

select segment_name,segment_type,tablespace_name from user_extents where segment_type='LOBSEGMENT';

SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME

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

SYS_LOB0000349415C00003$$ LOBSEGMENT USERS

Whenever we need to move LOBSEGMENT.

Before Move, Get informations:

select TABLE_NAME, COLUMN_NAME, STORAGE_TYPE from USER_XML_TAB_COLS where table_name='TMP01';

TABLE_NAME COLUMN_NAME STORAGE_TYPE

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

TMP01 XML_DATA CLOB

column table_name format a30

column column_name format a30

column segment_name format a30

select table_name,column_name,segment_name from user_lobs;

TABLE_NAME COLUMN_NAME SEGMENT_NAME

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

TMP01 SYS_NC00003$ SYS_LOB0000349415C00003$$

After that,use ALTER TABLE MOVE LOB command...

Example:

alter table tmp01 move lob (XML_DATA.XMLDATA) store as SYS_LOB0000349415C00003$$ (tablespace USERS);

Or

alter table tmp01 move lob (SYS_NC00003$) store as SYS_LOB0000349415C00003$$ (tablespace USERS);

If you need to move LOBSEGMENT of some tables for moving extents on tablespace(Assume: you need to resize datafile, but can't.... you want to do like "alter table ... move ")

It's a better way if you move it(lobsegment) to another tablespace else (different tablespace).

alter table tmp01 move lob (XML_DATA.XMLDATA) store as SYS_LOB0000349415C00003$$ (tablespace NEW_TABLESPACE);

alter table tmp01 move lob (SYS_NC00003$) store as SYS_LOB0000349415C00003$$ (tablespace NEW_TABLESPACE);