I have a table IMAGE with ID number and IMAGE blob columns. Size of this table is 25 GB. I ran an update statement to set the blob to NULL, basically deleting just the image to reclaim some space. After running the update I am unable to see the free space in tablespace, I am not sure why the DBA_FREE_SPACE is not showing the free space which I got after updating 25000 records with around 150K image size each which in total should be around 3.5 GB. Actually I wanna make sure that this will help us adding more images in the same table and I want to see the free space allocated after the update in that BLOB segment. Can anyone please help me finding out what else need to be done in order to get that free space?
Oracle version 10.2 OS: Linux
I am checking the free space using:
select sum(bytes)/1024/1024 from dba_free_space where tablespace_name = 'BLOB_TS';
Yes Sir, I did :)
I checked manually also whether the update actually deleted the images or not and it did. Dont know what else to check.
Before running update, I ran the query on DBA_FREE_SPACE for the tablespace in which my LOB is stored and the free space was around 100 Mb and after running the update also the DBA_FREE_SPACE is showing me 100 MB of free space, like nothing ever happened.
Excellent article, didn't know that the undo space also get stored in the lob segment itself. As I said, I deleted around 3.5 GB of images and when I ran:
select bytes from dba_segments where segment_name ='<lob segment name>' and owner ='<table owner>';
select sum(dbms_lob.getlength (<lob column name>)) from <table_name>;
The difference of these two SQL was arnd 4 GB, which I believe is UNDO of my last update. And the retention is set to 900 for this lob segment, so I believe even if I don't shrink it, when I add new images this space can be used because the UNDO is expired. Now the question is if I run shrink on this LOB segment, will it release this EXPIRED UNDO and the space can be viewed using DBMS_SPACE.SPACE_USAGE and DBA_FREE_SPACE?
I am little confused that if I run shrink on it, will the free space (expired undo) will be completely deallocated from the segment and given back to tablespace so that other segments in that tablespace can use it OR will it just mark them as FREE in the same segment so that data in the same segment can use it??
Once the undo is not required by Oracle, space of 'deleted' lob would become 'free' and it is viewable from DBMS_SPACE.SPACE_USAGE.
Once the online shrink is done, the high water mark of lob segment will be moved. Those space would be free for grab. It would be used by other objects sharing the tablespace or reused by the same object - segment.
So here is some of the figures:
output from dbms_space.space_usage:
FS1 Blocks = 0 Bytes = 0
FS2 Blocks = 0 Bytes = 0
FS3 Blocks = 0 Bytes = 0
FS4 Blocks = 103627 Bytes = 848912384
Full Blocks = 2836737 Bytes = 23238549504
SQL>select bytes,blocks from dba_segments where segment_name = 'SYS_LOB0000006421C00003$$';
SQL> select sum(dbms_lob.getlength(data)) "bytes" from image;
According to DBMS_SPACE, I have 103627 blocks with 75-100% free space, which is total 848912384 bytes and the total number of blocks for this segment is 2836737 which is of 23238549504 bytes total.
DBA_SEGMENTS says, total blocks allocated are 3290752 of 26957840384 bytes.
The actual length of the LOB data in image table, using dbms_lob is 22623818472 bytes.
I already DEALLOCATED all the UNUSED space, so there is nothing above HWM.
Why dba_segment shows 3290752 total blocks allocated where as dbms_space shows 2836737. If total bytes in dba_segment is 26957840384 and the size of lob size using dbms_lob is 22623818472 then why the FREE space bytes in DBMS_SPACE is only 848 MB?? It should be around 4GB.
I am wondering where is my space? Or am I doing something wrong here.
Please help me.
Edited by: Daljit on Jun 30, 2009 1:11 PM
There is a bug that causes releasing space frustration using "shrink". Maybe double check the space using dbms_space.UNUSED_SPACE, which checks the unused space above the high water mark in a segment. Bug 5565887 SHRINK SPACE IS REQUIRED TWICE FOR RELEASING SPACE.
Assuming in your output of dbms_space.space_usage, "FS4 BLOCK"is "Number of blocks that has at least 75 to 100% free space", "FULL BLOCKS" is "Total number of blocks that are full in the segment". Then all these blocks 2940364 are occupied with lob data and can not be used by other objects. Because space_usage doesn't count things like segment header block, extend map block, the number is smaller than dba_segment. These blocks are used below the high water mark. You could check space blocks under HWM with dbms_space.free_blocks.
Hope this helps.
Thanks for the reply.
LiangGangYu: I think it's hard to add up the figures from different sources and match it with some other, because of all the internal overhead you never know how oracle is going to use the blocks. So I stopped comparing all those figrures, as I know now that all the free space is there under HWM in the same LOB segment. But the main question is why DBMS_SPACE.SPACE_USAGE shows only 848 MB of free space where as there is a huge difference between the spce allocated and used which actually match with the image size which I deleted yesterday (around 3.5 GB). Also dbms_space.free_blocks doesn't work with ASSM tablespaces, the only option is DBMS_SPACE.SPACE_USAGE
As saravanan described, we can run move or shrink to get that space but I am more curious to find out why the dbms_space is not showing me the expected free space. I have all those images which I deleted on file system (I have a prog to unload images from DB to file system) and the size of all that is around 3.5GB so why after that delete the dbms_space is still showing 848 MB of free space as compare to 3.5 GB??
I can reclaim that space by running move and shrink but both has their own ramifications, like move can cause serous corruption on LOB as per the know bug 4450606 and shrink can also cause LOB corruption (below 10.2.0.3) moreover shrink will create a huge amount of redo and undo which need more space and manageability and it make it tooooo slow.
Edited by: Daljit on Jun 30, 2009 4:03 PM
The "UNDO" depends on either the PCTVERSION or RETENTION parameters of the LOB column. By default PCTVERSION is 10 which means reserver 10% of the space in the LOB segment for "UNDO". Retention is time based.
To see the size of the LOB segments use DBA_LOBS and join the SEGMENT_NAME to DBA_SEGMENTS and get the bytes size. Also retrieve the tablespace name because the location of the LOB may not necessarily be the same as the table's segment.
Use a sum of the DBMS_LOB.GETLENGTH query to work out the actual size of the LOB data within the column for all the rows. There is no other way to free the space apart from a reorg using a shrink (for ASSM only) or ALTER TABLE MOVE LOB. For both they may take some time depending on the amount of data. I suggest you try this in a test environment first. Also you may lock out the table when performing a reorg so you need to test the length of time it will take. Once the reorg is finished you will need to rebuild the LOB indexes - check for invalid/unusable objects anyway.
Hi Deljit, I see this on my 10.2.0.4. After I delete LOB, dbms_space.space_usage still reports the same thing after retention period. Is that also what you see? Then I insert LOBs into the same segment, old space is reused - during which no new block/extent is allocated, but a new extent is allocated when a single extra insert happens. This, to me, is like the internal LOB management that mentioned in metalink article- hard to get know everything at this point. After all, Oracle RDBMS is not open-source and source code for dbms_space is not directly human readable.
I also notice a wrong calculation that dbms_space.space_usage does after shrink a lob segment after deletion. It missed out a few extents - or tried to calculate the extents already released. Maybe a defect that I would ask Oracle Support for. After shrink, some previous lob extents will be released, therefore more free space should be able to notice through dba_free_space.
Thanks Liang, that is what I am seeing. For me, when I deleted lob, after that dbms_space.space_usage shows that it has only 848 MB of free space whereas I deleted arnd 3.5 GB of images. And for you, when you reinserted data then it reuse the whole space without creating more extents, means the space is there but didn't report properly thru dbms_space package. I will do a test of shrinking it and will see if it release 848 MB or 3.5 GB of free space out of it. If it release 3.5GB then it means space_usage was not showing the free space properly otherwise dont know after deleting 3.5 GB of data howcome only 848MB is left as free space in LOB, where I am not using any compression in database.
Thanks Gopal veere for the reply but if you read the post carefully, you will find that the main question is to find out where to look for that space not how to get that space. I am trying to find out why oracle is not showing me the space for the images i just deleted. I know shrink and move will reclaim that space from LOB but I wanna know first that how to look for that space and where to find more accurate figure about the free space in lob. Also read about the bugs I mentioned related to move and shrink.
Thanks to all who replied.
Edited by: Daljit on Jul 1, 2009 8:01 AM