Reclaiming LOB space

Oracle 10gR2 introduced the ability to reclaim unused LOB space within a table using the ALTER TABLE ... SHRINK SPACE command. However as is often the case with Oracle, things are a little more complicated than they first appear and there are a number of important things to note:

  • The table must reside in an ASSM tablespace.
  • The operation is not recorded in the v$session_longops view, so once started there is no way of knowing how long the operation will take, or how near to completion it is.
  • Excessive redo generation - If you have 10GB of LOB data, then Oracle will generate 20Gb of redo data((a copy for the delete undo image[1] and a copy for the insert redo image) which in turn will need to be archived.

How much space can be reclaimed?

The amount of space being used by a LOB column can be found by using the following query:

SELECT s.bytes FROM dba_segments s JOIN dba_lobs l USING (owner, segment_name)
WHERE l.table_name = '&table_name';

The actual size of the LOBs themselves can be found as follows:

SELECT nvl((sum(dbms_lob.getlength( &lob_column ))),0) AS bytes FROM &table_name;

The difference between the two figures above is the approximate amount of space that can be reclaimed.

Reclaiming the space

There are two forms of the command, the first of which will shrink the spaced used for a specified LOB column:

ALTER TABLE &table_name> LOB (&lob_column) (SHRINK SPACE;

And the second, which shrinks all dependent objects (including LOBS[2]) along with the data within the table itself:



[1] Yes, undo data is protected by redo in the same way as other database data. For more information see the article "Redo and Undo" by Tom Kyte.

[2] In Oracle 10g Release 1 shrinking a table with the CASCADE option does NOT shrink LOB segments for that table. This feature was only introduced in 10g Release 2.

  • Pramod Dhulkhed


    ALTER TABLE &table_name> LOB (&lob_column) (SHRINK SPACE;

    needs to be executed with “modify” option.

    ALTER TABLE &table_name> MODIFY LOB (&lob_column) (SHRINK SPACE;