DBA Tips Archive for Oracle

  

Search & Win   click me  

Reclaiming Unused LOB Space

by Jeff Hunter, Sr. Database Administrator

Contents

  1. Overview
  2. Example
  3. Troubleshooting


Overview

Manually reclaiming unused space in a normal Oracle table is a fairly simple process and can be performed online starting with Oracle 10g R1 using: 

SQL> ALTER TABLE <table_name> SHRINK SPACE COMPACT;

For example, to reclaim space from the table mytable: 

SQL> ALTER TABLE mytable ENABLE ROW MOVEMENT;

Table altered.

SQL> ALTER TABLE mytable SHRINK SPACE;

Table altered.

What if, however, the table contains a LOB column that contains significant unused space? Starting with Oracle 10g R2, Oracle provides a solution that allows users to reclaim LOB space (LOB data and LOB index) and that is the topic of this article.

The syntax used to reclaim LOB space is: 

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

  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 - the focus of this article. This new feature allows users to remove the deleted and free space altogether from a LOB segment and LOB index.


Example

  1. Example binary image file to load into BLOB column:

      iDevelopment_info_logo_2.tif

  2. Create example table to store binary images into a LOB column: 

    DROP TABLE test_lob CASCADE CONSTRAINTS
/

Table dropped.


CREATE TABLE test_lob (
      id                NUMBER
    , file_name         VARCHAR2(45)
    , image             BLOB
    , timestamp         DATE
)
/

Table created.

  3. Create Oracle sequence to track images: 

    DROP SEQUENCE test_lob_seq
/

Sequence dropped.


CREATE SEQUENCE test_lob_seq
/

Sequence created.

  4. Create Oracle directory for binary image files: 

    CREATE OR REPLACE DIRECTORY images_dir
AS '/u03/oradir/images'
/

Directory created.

  5. Create a sample PL/SQL procedure to load images from the file system to a BLOB column:

    load_blob_from_file_image.sql
    CREATE OR REPLACE PROCEDURE Load_BLOB_From_File_Image
AS

    dest_loc    BLOB;
    file_name   TEST_LOB.FILE_NAME%TYPE  := 'iDevelopment_info_logo_2.tif';
    src_loc     BFILE := BFILENAME('IMAGES_DIR', file_name);

BEGIN

    -- +-------------------------------------------------------------+
    -- | INSERT INITIAL BLOB VALUE (an image file) INTO THE TABLE    |
    -- +-------------------------------------------------------------+
    INSERT INTO test_lob (id, file_name, image, timestamp)
        VALUES (test_lob_seq.nextval, file_name, empty_blob(), sysdate)
        RETURNING image INTO dest_loc;

    -- +-------------------------------------------------------------+
    -- | OPENING THE SOURCE BFILE IS MANDATORY                       |
    -- +-------------------------------------------------------------+
    DBMS_LOB.OPEN(src_loc, DBMS_LOB.LOB_READONLY);

    -- +-------------------------------------------------------------+
    -- | OPENING THE LOB IS OPTIONAL                                 |
    -- +-------------------------------------------------------------+
    DBMS_LOB.OPEN(dest_loc, DBMS_LOB.LOB_READWRITE);

    -- +-------------------------------------------------------------+
    -- | SIMPLY CALL "loadfromfile" TO LOAD FILES INTO A LOB COLUMN  |
    -- +-------------------------------------------------------------+
    DBMS_LOB.LOADFROMFILE(
          dest_lob => dest_loc
        , src_lob  => src_loc
        , amount   => DBMS_LOB.getLength(src_loc));

    -- +-------------------------------------------------------------+
    -- | CLOSING ANY LOB IS MANDATORY IF YOU HAVE OPENED IT          |
    -- +-------------------------------------------------------------+
    DBMS_LOB.CLOSE(dest_loc);
    DBMS_LOB.CLOSE(src_loc);

    COMMIT;

END;
/

    
SQL> @load_blob_from_file_image.sql

Procedure created.

  6. Run script to load 1,000 images (25MB) to LOB column: 

    BEGIN
    FOR i IN 1 .. 1000
    LOOP
        Load_BLOB_From_File_Image();
    END LOOP;
END;
/

PL/SQL procedure successfully completed.


SELECT count(1) FROM test_lob;

  COUNT(1)
----------
      1000

  7. Determine name and size of the LOB column: 

    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 = 'TEST_LOB';

TABLE_NAME   COLUMN_NAME    SEGMENT_NAME                      BYTES
------------ -------------- ---------------------------- ----------
TEST_LOB     IMAGE          SYS_LOB0000054349C00003$$    25,165,824


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

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

  8. Now let's delete all images (BLOBS) and see what the size is after: 

    DELETE FROM test_lob;

1000 rows deleted.


COMMIT;

Commit complete.


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 = 'TEST_LOB';

TABLE_NAME   COLUMN_NAME    SEGMENT_NAME                      BYTES
------------ -------------- ---------------------------- ----------
TEST_LOB     IMAGE          SYS_LOB0000054349C00003$$    25,165,824


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

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

  9. Hmmm, so it looks like the images are gone, but the space is still allocated within the LOBSEGMENT (25MB). Ok, so maybe if we insert data back into the table (more importantly, the LOB column), that space will be recycled: 

    BEGIN
    FOR i IN 1 .. 1000
    LOOP
        Load_BLOB_From_File_Image();
    END LOOP;
END;
/

PL/SQL procedure successfully completed.


SELECT count(1) FROM test_lob;

  COUNT(1)
----------
      1000


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 = 'TEST_LOB';

TABLE_NAME   COLUMN_NAME    SEGMENT_NAME                      BYTES
------------ -------------- ---------------------------- ----------
TEST_LOB     IMAGE          SYS_LOB0000054349C00003$$    50,331,648


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

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

  10. Ok, so I was wrong. The LOB space did not get recycled with the new data. Let's now pull out the big guns and manually shrink the LOB segment to free up the space immediately: 

    DELETE FROM test_lob;

1000 rows deleted.


COMMIT;

Commit complete.


ALTER TABLE 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 = 'TEST_LOB';

TABLE_NAME   COLUMN_NAME    SEGMENT_NAME                      BYTES
------------ -------------- ---------------------------- ----------
TEST_LOB     IMAGE          SYS_LOB0000054349C00003$$        65,536


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

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

  11. Now that looks better!


Troubleshooting


Copyright (c) 1998-2012 Jeffrey M. Hunter. All rights reserved.

All articles, scripts and material located at the Internet address of http://www.idevelopment.info is the copyright of Jeffrey M. Hunter and is protected under copyright laws of the United States. This document may not be hosted on any other site without my express, prior, written permission. Application to host any of the material elsewhere can be made by contacting me at jhunter@idevelopment.info.

I have made every effort and taken great care in making sure that the material included on my web site is technically accurate, but I disclaim any and all responsibility for any loss, damage or destruction of data or any other property which may arise from relying on it. I will in no case be liable for any monetary damages arising from such loss, damage or destruction.

Last modified on
Saturday, 18-Sep-2010 17:38:20 EDT
Page Count: 19094