Hi,

I will explain How to Move Lob Segment to Another Tablespace in Oracle in this post.

Move Lob Segment to Another Tablespace

Sometimes you may need to drop any tablespace, so you need to move all objects in this tablespace. Moving lob segments is difficult according to table and index.

Check any table if it has any lob segment using the following script.

SELECT table_name, column_name, segment_name
FROM dba_segments a JOIN dba_lobs b
USING (owner, segment_name)
WHERE b.table_name = 'TABLE_NAME';

You can find the table name of any lob segment using the following script.

SELECT table_name, column_name, segment_name, a.bytes
FROM dba_segments a JOIN dba_lobs b
USING (owner, segment_name)
WHERE a.segment_name = '&logsegment';

Move Lob Segment to Another Tablespace in Oracle

You can move any lob segment to another tablespace as follows.

ALTER TABLE OWNER.TABLE_NAME MOVE LOB(LOB_COLUMN) STORE AS (TABLESPACE NEW_TABLESPACE_NAME);

You can generate the moving scripts for lob segments as follows.

select 'ALTER TABLE '||owner||'.'||table_name||' MOVE LOB( '||column_name||') STORE AS (TABLESPACE NEW_TABLESPACE_NAME) parallel 96;' from dba_lobs where tablespace_name='OLD_TABLESPACE_NAME';

Move Lob Indexes to Another Tablespace in Oracle

You can use the following scripts for Lob segment, lob indexes and lob partitions to move this lob segments to new tablespace.

select 'ALTER TABLE '||owner||'.'||table_name||' MOVE LOB( '||column_name||') STORE AS (TABLESPACE NEW_TABLESPACE_NAME) parallel 96;' from dba_lobs where tablespace_name='OLD_TABLESPACE_NAME';

select 'alter index '||index_owner||'.'||index_name||' rebuild partition '||partition_name||' tablespace NEW_TABLESPACE_NAME;' from dba_ind_partitions where tablespace_name='OLD_TABLESPACE_NAME';


select 'ALTER TABLE '||table_owner||'.'||table_name||' MOVE partition '||Partition_name||' lob('||column_name||')'||' STORE AS (TABLESPACE NEW_TABLESPACE_NAME) ;' from dba_lob_partitions where TABLESPACE_NAME = 'OLD_TABLESPACE_NAME';

You can read the following post to learn how to move tables and Index to new tablespace.

How to Move Tables, Index Rebuild, Partitions and Subpartitions to Another Tablespace in Oracle