Wednesday, December 9, 2015

Deleted BLOB data still taking up space in Oracle

If you have database tables with  CLOB/BLOB data columns, one thing you should know is that deleting these values via normal delete queries will not work completely. That is the values will of course be removed from the tables, but the space taken up by these values (number of bytes) will remain the same. This might not be visible if you are processing small amount ( less than 4 mb) data, but once  you start processing large messages this will eventually become an issue.

The reason for this is LOB values only keep a reference. In order to free up the space consumed by these LOBS you could follow below steps.

1. For analysing purposes, execute the following command which would list down the number of bytes of LOB columns. Replace 'YOUR_VALUE' with your custom values.

select  sum(bytes),dba_extents.owner , dba_extents.segment_type, dba_extents.tablespace_name
 , dba_lobs.table_name, dba_lobs.column_name
 from dba_extents , dba_lobs
 where dba_extents.tablespace_name like 'YOUR_VALUE'
 and segment_type in ('LOBSEGMENT','LOBINDEX')
 and dba_extents.owner ='YOUR_VALUE'
 and dba_lobs.table_name in ('YOUR_VALUE','YOUR_VALUE')
 and dba_lobs.owner=dba_extents.owner
 and dba_lobs.segment_name=dba_extents.segment_name
 group by segment_type, dba_extents.owner, dba_extents.segment_name, dba_extents.tablespace_name,dba_lobs.table_name ,dba_lobs.column_name
 order by dba_extents.segment_name;

So the solution you could perform is as below.

1. Create a temporary table and move the required data to this table. ( This temporary table should be created in a separate table space associated with the separate data file.)
2. Once the data move is completed, truncate the original table.
3. Once the truncate is completed, you can insert the required data from the temporary table to the original table.
4.Once data transfer is completed, truncate and drop the temporary table.

If you execute above query before step 1, after step 2 and after step 3  you should be able to see the difference in the number of bytes, which suggests that the actual deleted value space is released.