Thursday, June 14

Reclaiming LOB space in Oracle

Reclaiming space in Oracle can sometimes be a bit of a "problem", not really a problem it just works in a funny way. It's a quite common question I get and users are usually happy with a manual alter table table_name shrink space compact;, but what do we do for lobs?
We need a manual reclaim for the lob column.

A little demo:
(spinner1)oracle@spinner[~/lob_test]$ rsqlplus hlinden/hlinden

SQL*Plus: Release 10.2.0.2.0 - Production on Thu Jun 14 12:19:02 2007

Copyright (c) 1982, 2005, Oracle. All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

-- Create a table and sequence to play with
HLINDEN@spinner1> create table lob_test (id number, data blob);

Table created.

HLINDEN@spinner1> create sequence lob_test_seq;

Sequence created.

-- Load 50 rows with 1.5Mb blobs (see code bellow)
HLINDEN@spinner1> @lobload

PL/SQL procedure successfully completed.

-- Find out what our lob segment is called
HLINDEN@spinner1> select object_name,object_type from user_objects where
2 created>sysdate-interval '5' minute and object_type='LOB';

OBJECT_NAME OBJECT_TYPE
------------------------------ -------------------
SYS_LOB0000199575C00002$$ LOB

-- Display the current size of the lob segment
HLINDEN@spinner1> select round(sum(bytes)/1024/1024) Mb from user_segments where
segment_name='SYS_LOB0000199575C00002$$';

MB
----------
75

-- Ok, let's delete those blobs and see what the size is after
HLINDEN@spinner1> delete from lob_test purge;

50 rows deleted.

HLINDEN@spinner1> select round(sum(bytes)/1024/1024) Mb from user_segments where
segment_name='SYS_LOB0000199575C00002$$';

MB
----------
75

-- Still 75Mb, hm, perhaps it recycled if we insert more data?

HLINDEN@spinner1> @lobload

PL/SQL procedure successfully completed.

HLINDEN@spinner1> select round(sum(bytes)/1024/1024) Mb from user_segments where
segment_name='SYS_LOB0000199575C00002$$';

MB
----------
150

-- Nope, not recycled. We need to issue a shrink command to free up the
-- space immediately

HLINDEN@spinner1> delete from lob_test;

50 rows deleted.

HLINDEN@spinner1> alter table lob_test modify lob (data) (shrink space);

Table altered.

HLINDEN@spinner1> select round(sum(bytes)/1024/1024) Mb from user_segments where
segment_name='SYS_LOB0000199575C00002$$';

MB
----------
0
-- All gone!
My simple blob loading code:
DECLARE
src_file BFILE := bfilename('TMP', 'data.dat');
dst_file BLOB;
lgh_file BINARY_INTEGER;
cur_id NUMBER(10);
BEGIN
FOR i IN 1..50
LOOP
INSERT INTO lob_test(id,data) VALUES(lob_test_seq.nextval,empty_blob())
RETURNING id into cur_id;
-- lock record
SELECT data INTO dst_file FROM lob_test WHERE id=cur_id FOR UPDATE;

dbms_lob.fileopen(src_file, dbms_lob.file_readonly);
lgh_file := dbms_lob.getlength(src_file);
dbms_lob.loadfromfile(dst_file, src_file, lgh_file);
dbms_lob.fileclose(src_file);
END LOOP;
END;
/

7 comments:

Anonymous said...

Hello.

"It's a quite common question I get and users are usually happy with a manual alter table table_name shrink space compact; but what do we do for lobs?"

I think ALTER TABLE table_name SHRINK SPACE CASCADE shrinks all dependent segments of the object (including LOB segments)

Cheers.

Carlos.

MrTree said...

Thanks for this blog entry and Carlos, thanks for the tip.

The shirnk space idea is good, but I'm not sure if the CASCADE is really required or not for LOBs. I do know that "ALTER TABLE tablename SHRINK SPACE" was only inroduced in 10g, and whats more, the segment must be in an ASSM tablespace.

Anonymous said...

There are 2 other problems shrinking blob segments:
1. If you want to shrink a table with 26 Gb blobs, il will generate 26 Gb redo space. So if you want to reclaim space, you need to have disk space.
2. This solution is only ok for small databases. To shrink a table of 26Gb, it will take 17 hours. We have a 2.6Tb table in our database, to recover space used by nulled blobs / deleted rows, we would need 2.6 Tb free space for redos and it would take ~1700 hours( ~70 days).

voijh said...

One other thing I noticed, that for large LOBS which would get cleaned up by the ALTER TABLE abc MODIFY LOB (myColumn) (SHRINK SPACE); command... it doesnt' show up in the v$long_ops view, so i can't tell how long it would take. My test run is over several hours and counting. Does anybody have tips on how to check the progress of such a delete?

Voy said...

as LOB shrinking is so time consumed operation can't you just do CTAS, drop the lob_table and rename the new one to original name?

regards
Voy

Anonymous said...

The other day I was working on a 4rx project at work and I was having some issues with reclaiming space in Oracle, so I will try to do this, thanks!

Blogger said...

Are you looking to get money from your websites/blogs by popup advertisments?
If so, have you ever used Clickadu?