Re-Org Tablespace with Lob Columns

It is often seen that the tables with LOB columns consume space much more then actual data,there are ways to reclaim wasted space

1) import/export
2) datapump which is faster then imp/exp and more flxible
3) moving LOB objects to different tablespace.

In this article i will re-org using temporary tablespace and moving it back to original tabespace.

First we need to identify the lob columns and space consumed

select dl.table_name,dl.column_name,dl.segment_name,ds.segment_type,
ds.bytes,ds.blocks
from dba_lobs dl, dba_segments ds
where ds.segment_type = 'LOBSEGMENT'
and ds.segment_name = dl.segment_name
order by bytes desc

Generate script, here i have created lob_ts_temp tablespace with nologging and autoextended on , spool it to a file and execute

select 'alter table ' || dl.owner ||'.'|| dl.table_name || ' move lob (' || column_name || ') store as (tablespace lobs_ts_temp);'
from
dba_lobs dl,
dba_segments ds
where 1=1
and ds.segment_type = 'LOBSEGMENT'
and ds.segment_name = dl.segment_name
and dl.tablespace_name='LOBS_TS';

execute the script generated by the above sql, once the script is finished check the space again and you will see how much space was wasted.

select dl.table_name,dl.column_name,dl.segment_name,ds.segment_type,
ds.bytes,ds.blocks
from dba_lobs dl, dba_segments ds
where ds.segment_type = 'LOBSEGMENT'
and ds.segment_name = dl.segment_name
order by bytes desc;

Before dropping make sure that the tablespace is empty,
select * from dba_tablespace_usage_metrics where tablespace_name='LOBS_TS';

You can drop original tablespace using below command
drop tablespace lob_ts including contents and datafile;

Now, if you want you can re-create original tablespace and move the objects back or you can keep the same tablespace, but you have to plan earlier whether you want to re-create original tablespace or you want to move and keep the objects in new tablespace, create new or temporary tablespace as per requirement.

Moving tablespace makes indexes unuseable so identify and rebuild indexes, use below query to generate the script

select 'alter index ' || owner ||'.'||index_name || ' rebuild online parallel 2;' from dba_indexes
where status='INVALID'

I hope this article will be useful, please comment.