RE: Release space used by empty BLOB segments

From: Uzzell, Stephan <SUzzell_at_MICROS.COM>
Date: Mon, 10 Oct 2011 17:06:33 +0000
Message-ID: <>

Hi Jack,

You might look into Bug 9532911: LOB SHOWING INCORRECT DATA ON DATA GUARD STANDBY SITE as part of your troubleshooting...

Stephan Uzzell

-----Original Message-----

From: [] On Behalf Of Sent: Monday, 10 October, 2011 13:02
Cc: ORACLE-L; Subject: Re: Release space used by empty BLOB segments

Have you considered "Alter Table <Table> Shrink Space Cascade"? I just discovered it recently and it's very useful. I've used it on several very large tables, one with a huge LOB segment. It compacts the table segment, as well as all LOB and index segments, releasing the unused space. The excellent part is that the table can still have DML activity - it remains open and available.

The tables on which I've used it were multi tens of millions of rows, occupying up to 35 GB. It takes a LONG time for tables that large - 9 to 12 hours - but works like a charm. The only downside I've discovered is that it appears to give Logical Standby DBs indigestion and they simply will not perform SQL Apply. I'm working on discovering why, but so far no luck.

Anyway, I'm very happy with this and wish I'd discovered it years ago - first available in 10gR1.

We're on Enterprise 64 bit on Linux.

Jack C. Applewhite - Database Administrator Austin I.S.D. - MIS Department
512.414.9250 (wk) / 512.935.5929 (pager)

From: Yechiel Adar <> To: ORACLE-L <> Date: 10/10/2011 07:19 AM

Subject:        Release space used by empty BLOB segments
Sent by:

I have a partitioned (by week) table that holds data and images. The data is kept for 7 years (banking) while the images are kept only for one year.
The images are deleted by updating the fields to empty blobs. The tables has local indexes and a bunch of global indexes. The problem is that the empty blob segments are not released after their content id deleted.

  1. How can I release the unused space without making the global indexes unusable?
  2. Preferably while the users are working (but this not a must)?


Oracle Database 10g Enterprise Edition Release - 64bi PL/SQL Release - Production
CORE Production
TNS for Linux: Version - Production NLSRTL Version - Production


Yechiel Adar



-- Received on Mon Oct 10 2011 - 12:06:33 CDT

Original text of this message