Delete BLOB data and the space occupied by it

Question

We have Table in which we are storing JSON file in BLOB object in securefile

Datatype : JSON_FILES BLOB

LOB (JSON_FILES) STORE AS SECUREFILE (
  TABLESPACE  J_FILES
  ENABLE      STORAGE IN ROW
  CHUNK       8192
  NOCACHE
  LOGGING
      STORAGE    (
                  INITIAL          104K
                  NEXT             1M
                  MINEXTENTS       1
                  MAXEXTENTS       UNLIMITED
                 ))

However when i delete the rows from the table using Delete statement . Rows get deleted but the space taken by BLOB is present in some segment .

Delete from FILE_TABLE WHERE FILE_ID Between 10 and 11000


SELECT  SEGMENT_NAME, TABLESPACE_NAME, SUM(BYTES) / 1024 / 1024 / 1024 SIZE_IN_GB
FROM    DBA_SEGMENTS
WHERE   OWNER = 'ABC'
AND     TABLESPACE_NAME = 'FILES_TABLESPACE'
GROUP BY SEGMENT_NAME, TABLESPACE_NAME

SYS_LOB0001543022C00007$$   FILES_TABLESPACE **608.9727783203125**
FILE_TABLE  FILES_TABLESPACE 2.6875

Any solution on Oracle 19c

Answer 1

Usually when you delete something in a table (segment), the space allocated to the table remains the same in the tablespace you just have more freespace in the table. Without looking at the manual to check I would expect segments holding blob (or clob) would behave the same way. Now if you want to shrink a segment to free the space it used, that's a different question.

Depending upon your circumstances, you might get some joy with (credit to oracel-base for the info):

ALTER TABLE table_name MODIFY LOB(lob_column) (SHRINK SPACE);
ALTER TABLE table_name MODIFY LOB(lob_column) (SHRINK SPACE CASCADE);