Saturday, March 14, 2009

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

1 comment:

OLINEX said...

Thx a lot. Using your guide,I've generated script to move all lobsegment to an other tablespace with this statement :

select 'alter table '||owner||'.'||table_name||' move lob ('||column_name||') store as '||segment_name||' (tablespace NEW_TBS);'
from all_lobs
where segment_name in (select segment_name from dba_segments where segment_type='LOBSEGMENT' and tablespace_name='OLD_TBS')