Monday, May 2, 2011

MOVING LOB SEGMENTS FROM ONE TABLESPACE TO ANOTHER

Moving a lob segment from on tablespace to another is a little tricky ,And it is little different than moving a table from a tablespace to another.

The Syntax for moving LOB segments would be

alter table tablename move lob (CONTENTS) store as (tablespace tablespacename);

EXAMPLE:

alter table sys.abc move lob (CONTENTS) store as (tablespace users);

The index associated with the lob segments cannot be moved to another tablespace that is indifferent from the lob segment tablespace.