Moving LOBS along with Tables

Moving a regular table to a different tablespace is straight forward.

Alter table move tablespace  ;

And then rebuild the indexes as it becomes invalid. But what if the table has LOB segments? Similar to INDEX, the LOB doesn’t moves where the table just relocated.

You can run the followings to see where it’s located. And take decision where do you want the LOB to move.

select owner, table_name, column_name,tablespace_name from dba_lobs where segment_name in (select segment_name from dba_segments where tablespace_name = 'SCOTT_DATA' );

Example:

select owner, table_name, column_name,tablespace_name from dba_lobs where segment_name in (select segment_name from dba_segments where tablespace_name like '%DATA' )

and table_name like '%LOG%'

and owner like 'SL%';

If you want to move only lob segment to a new tablespace then your command will be,

ALTER TABLE table_name MOVE LOB(lob_columnname) STORE AS (TABLESPACE new_tablespace_name);

We can also move table and LOB altogether with the below command:

ALTER TABLE table_name MOVE
TABLESPACE new_tablespace
LOB (lobcol1,lobcol2) STORE AS
(TABLESPACE new_tablespace);