Home > Database, Oracle > Reclaiming Unused LOB Space

Reclaiming Unused LOB Space

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

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. Create example table to store binary images into a LOB column.
connect scott/tiger

Connected.

DROP TABLE test_lob CASCADE CONSTRAINTS

/

Table dropped.

CREATE TABLE test_lob (

    id                NUMBER

  , file_name         VARCHAR2(45)

  , image             BLOB

  , timestamp         DATE

)

/

Table created.

  1. Create Oracle sequence to track images.
DROP SEQUENCE test_lob_seq

/

Sequence dropped.

CREATE SEQUENCE test_lob_seq

/

Sequence created.

  1. Create Oracle directory for binary image files.
CREATE OR REPLACE DIRECTORY images_dir

AS ‘/u04/app/oracle/lobs’

/

Directory created.

  1. 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.

  1. 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

  1. 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_LOB0000148988C00003$$    25,165,824

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

FROM test_lob;

BYTES

———-

16,706,000

  1. 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_LOB0000148988C00003$$    25,165,824

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

FROM test_lob;

BYTES

———-

0

  1. 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_LOB0000148988C00003$$    50,331,648

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

FROM test_lob;

BYTES

———-

16,706,000

  1. 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_LOB0000148988C00003$$        65,536

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

FROM test_lob;

BYTES

———-

0

  1. Now that looks better!

Troubleshooting

  • Pre-requisites

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.

The LOB segment must reside in an ASSM tablespace.

Row movement on a table is only required if you are moving rows from the table itself. It is not required if all you are doing is shrinking its LOB segment(s).

  • Excessive Redo Generated

Shrinking a LOB segment does generate redo. For example, shrinking a 36GB LOB will generate approximately 36GB of redo. Make certain you have an adequate amount of disk space for any archived redo log files before manually shrinking a LOB segment.

  • The CASCADE Option

The alter table <table_name> shrink space statement has an optional CASCADE clause that shrinks all dependent objects (including LOBS) along with the table data itself. For example:

ALTER TABLE test_lob ENABLE ROW MOVEMENT;

Table altered.

ALTER TABLE test_lob SHRINK SPACE CASCADE;

Table altered.

With Oracle 10g R1, when shrinking a table, the CASCADE option DOES NOT shrink LOB segments for that table.

With Oracle 10g R2 and higher, when shrinking a table, the CASCADE option DOES shrink all LOB segments (and indexes) for that table.

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: