LOBs (Large Objects) are often the silent space hogs in Oracle databases — especially when poorly managed or not regularly reorganized. In this post, I’ll walk you through how I reduced a massive in a real Oracle production environment using simple and efficient techniques — no downtime, no data loss.

The Challenge

A LOB column (C49733) in the table dbadeeds.EMP had ballooned to over 4.2 TB:

SELECT SEGMENT_NAME, TABLESPACE_NAME, BYTES/1024/1024/1024 GB
FROM DBA_SEGMENTS
WHERE SEGMENT_NAME LIKE 'SYS_LOB0000323358C00012$$'
  AND OWNER = 'DBADEEDS';

Result:

Segment Name	Tablespace	Size (GB)
SYS_LOB0000323358C00012$$	DEEDS_TBS	4296.25

---

Step 1: Identify the LOB Segment

We queried metadata to pinpoint the exact LOB segment associated with the column:

sqlCopyEditSELECT l.owner, l.table_name, l.column_name, l.segment_name, 
       l.tablespace_name, ROUND(s.bytes/1024/1024/1024, 1) size_Gb
FROM   dba_lobs l
JOIN   dba_segments s ON s.owner = l.owner AND s.segment_name = l.segment_name
WHERE  s.segment_name LIKE 'SYS_LOB0000323358C00012$$'
ORDER  BY 6 DESC;

---

Step 2: Create a Temporary Tablespace

To reorganize the LOB data, I created a temporary staging tablespace:

CREATE BIGFILE TABLESPACE DEEDS_TBS_TMP 
DATAFILE SIZE 50G 
AUTOEXTEND ON NEXT 1G 
MAXSIZE 4500G;

---

Step 3: Move the LOB Column to the New Tablespace

Now for the magic: move the table and LOB column online using parallel DML.

ALTER TABLE DBADEEDS.EMP 
  MOVE TABLESPACE DEEDS_TBS_TMP 
  LOB (C49733) 
  STORE AS (TABLESPACE DEEDS_TBS_TMP) 
  ONLINE PARALLEL 16;

After the move, we checked the segment size:

SELECT SEGMENT_NAME, TABLESPACE_NAME, BYTES/1024/1024/1024 GB 
FROM DBA_SEGMENTS 
WHERE SEGMENT_NAME LIKE 'SYS_LOB0000323358C00012$$' 
  AND OWNER = 'DBADEEDS';

Result:

Segment Name	Tablespace	Size (GB)
SYS_LOB0000323358C00012$$	DEEDS_TBS_TMP	4.0

✅ LOB segment successfully compacted!

---

Step 4: Move LOB Back to Original Tablespace

To maintain storage policy consistency, I moved the LOB back to its original tablespace:

ALTER TABLE DBADEEDS.EMP 
  MOVE TABLESPACE DEEDS_TBS 
  LOB (C49733) 
  STORE AS (TABLESPACE DEEDS_TBS) 
  ONLINE PARALLEL 16;

Final size remained 4 GB — confirming that the reorganization worked perfectly.

---

Step 5: Clean Up

Since the temporary tablespace is no longer needed:

sqlCopyEditDROP TABLESPACE DEEDS_TBS_TMP INCLUDING CONTENTS AND DATAFILES;

✅ Done. LOB segment is compacted and returned to its original location with minimal effort.

---

• 🔻 Space savings: From 4.2 TB → 4 GB
• 🧠 No data loss: Entire operation done online
• ⚡ Minimal impact: Thanks to PARALLEL and ONLINE clauses
• 💰 Reduced storage cost and improved backup speed

---

LOB segments tend to grow significantly over time due to fragmentation and retention. By leveraging LOB segment reorganization — especially with online move and temporary tablespace staging — you can dramatically reduce space and optimize performance with minimal downtime.

Don’t let your LOBs run wild!