Translate

Monday, 5 November 2012

How to reclaiming Unused LOB Space ??


SELECT table_name, column_name, segment_name, a.bytes
FROM dba_segments a JOIN dba_lobs b
USING (owner, segment_name)
WHERE b.table_name = 'NITI_TEST_LOB';

TABLE_NAME             COLUMN_NAME    SEGMENT_NAME                      BYTES
------------                    -------------- ---------------------------- ----------
NITI_TEST_LOB             IMAGE          SYS_LOB0000148988C00003$$    50,331,648


SELECT NVL((SUM(DBMS_LOB.GETLENGTH(image))),0) AS BYTES
FROM niti_test_lob;

     BYTES
----------
16,706,000

Use following command to manually shrink the LOB segment to free up the space immediately:


ALTER TABLE niti_test_lob MODIFY LOB (image) (SHRINK SPACE);

Table altered.


SELECT table_name, column_name, segment_name, a.bytes
FROM dba_segments a JOIN dba_lobs b
USING (owner, segment_name)
WHERE b.table_name = 'NITI_TEST_LOB';

TABLE_NAME     COLUMN_NAME             SEGMENT_NAME                      BYTES
------------             --------------                    ----------------------------           ----------
NITI_TEST_LOB     IMAGE                  SYS_LOB0000148988C00003$$        65,536


SELECT NVL((SUM(DBMS_LOB.GETLENGTH(image))),0) AS BYTES
FROM niti_test_lob;

    BYTES
----------
         0

The alter table <table_name> shrink space statement was introduced in Oracle 10g R1.
The ability to extend the SHRINK SPACE command to LOBs was introduced in Oracle 10g R2

No comments:

Post a Comment