You are hereMove LOBSEGMENT to different tablespace

Move LOBSEGMENT to different tablespace


By surachart - Posted on 22 March 2009

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);


Syndicate

Syndicate content

Follow DatabasesLA on Twitter

Who's online

There are currently 0 users and 3 guests online.

Estadisticas

Locations of visitors to this page

hidden hit counter