DBA Tips Archive for Oracle

  


Reclaiming Unused LOB Space

by Jeff Hunter, Sr. Database Administrator

Contents

Introduction

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


    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.

  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 '/u04/app/oracle/lobs' / 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_LOB0000148988C00003$$ 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_LOB0000148988C00003$$ 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_LOB0000148988C00003$$ 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_LOB0000148988C00003$$ 65,536 SELECT NVL((SUM(DBMS_LOB.GETLENGTH(image))),0) AS BYTES FROM test_lob; BYTES ---------- 0

  11. Now that looks better!

Troubleshooting

About the Author

Jeffrey Hunter is an Oracle Certified Professional, Java Development Certified Professional, Author, and an Oracle ACE. Jeff currently works as a Senior Database Administrator for The DBA Zone, Inc. located in Pittsburgh, Pennsylvania. His work includes advanced performance tuning, Java and PL/SQL programming, developing high availability solutions, capacity planning, database security, and physical / logical database design in a UNIX, Linux, and Windows server environment. Jeff's other interests include mathematical encryption theory, programming language processors (compilers and interpreters) in Java and C, LDAP, writing web-based database administration tools, and of course Linux. He has been a Sr. Database Administrator and Software Engineer for over 18 years and maintains his own website site at: http://www.iDevelopment.info. Jeff graduated from Stanislaus State University in Turlock, California, with a Bachelor's degree in Computer Science.



Copyright (c) 1998-2014 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
Thursday, 15-Mar-2012 13:53:39 EDT
Page Count: 54366