Does deleting the rows from table also release space of large objects in CLOB or BLOB columns?

Question

Size of large objects is not shown with table size where queried through dba_segments where table_name=abc so does deleting the rows from table also release space of large objects in CLOB or BLOB columns in those tables?

Answer 1

If you delete the rows having large LOB objects then oracle does not free the space to the tablespace automatically.

You will need to claim the space(size) which will be released to your tablespace using following query:

ALTER TABLE <YourTable> MODIFY LOB (<LobColumn>) (SHRINK SPACE);

You can calculate the total size of the LOB segment using the following query:

SELECT OWNER,SEGMENT_NAME,ROUND(SUM(BYTES)/1024/1024) "LOB size (mb)" 
FROM DBA_SEGMENTS
WHERE SEGMENT_NAME IN 
    (
    SELECT SEGMENT_NAME 
    FROM DBA_LOBS WHERE TABLE_NAME = <YOURTABLE>
    AND OWNER = <YOUROWNER>
    )
GROUP BY OWNER,SEGMENT_NAME; 

You can calculate the size before and after deletion of record and you will find no difference, but once you execute the aforementioned ALTER TABLE command (after deleting the rows), you will see difference in size using the above query.

Cheers!!