There may be situation where big chunks of LOB data got cleaned up/deleted.
Few days ago, we had an issue with the LOB replication with our replication software. Then we tested DMLs time to time on the table containing LOB. Although, finally we deleted all the test data, it resulted occupying 39 GB space on the tablespace where we have the LOB data.
col sname format a30
col tab format a30
col mb format 99,999,999
set pagesize 100
SQL> select us.segment_name sname, ul.table_name tab, sum(bytes)/1024/1024 mb
from user_segments us, user_lobs ul
where us.tablespace_name='DATA03'
and us.segment_name = ul.segment_name
group by us.segment_name, ul.table_name
order by 3 desc;
SNAME TAB MB
-------------------------- -------------------------- -------------------
SYS_LOB0000052400C00003$$ TEST_DATA 39,670
SYS_LOB0000052362C00005$ USER_FILE 25,923
SYS_LOB0000052365C00003$$ USER_IMAGE 9,313
I knew, I had only 1 MB of data on my TEST_DATA table at the moment.
SQL> SELECT SUM(DBMS_LOB.getlength(RESULT_DATA))/1024/1024 MB
FROM TEST_DATA;
MB
----------
1
Approach -1:
Then the following command had been used to shrink the unused space and reset the HWM. This feature is available from Oracle 10g.
SQL> ALTER TABLE TEST_DATA MODIFY LOB (RESULT_DATA) (SHRINK SPACE);
--- This may take few hours depending on the amount of unused space.
SQL> select us.segment_name sname, ul.table_name tab, sum(bytes)/1024/1024 mb
from user_segments us, user_lobs ul
where us.tablespace_name='DATA03'
and us.segment_name = ul.segment_name
group by us.segment_name, ul.table_name
order by 3 desc;
SNAME TAB MB
-------------------------- -------------------------- -------------------
SYS_LOB0000052362C00005$ USER_FILE 25,923
SYS_LOB0000052365C00003$ USER_IMAGE 9,313
SYS_LOB0000052400C00003$$ TEST_DATA 1
I have reclaimed 39669 MB of space!
To shrink other non-lob tables -
ALTER TABLE test SHRINK SPACE;
Approach - 2:
Another approach to reclaim unused space might be moving the object to another tablespace. This is how the HWM could be reset.
SQL> alter table table_name move lob(lob_column) store as (tablespace tablespace_name);
We have to rebuild all the indexes associated to the table as those will be unusable after moving.
One thing, shrinking is allowed only for those segments which use Automatic Segment Space Management.
