14 Replies Latest reply on Jul 1, 2009 1:03 PM by Daljit

    Reclaiming space from LOB


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

      Daljit Singh
        • 1. Re: Reclaiming space from LOB
          • 2. Re: Reclaiming space from LOB
            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.

            Daljit Singh
            • 3. Re: Reclaiming space from LOB
              "How to determine the actual size of the LOB segments and how to free the deleted/unused space above/below the HWM" from metalink 386341.1 can help you
              • 4. Re: Reclaiming space from LOB
                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??

                Daljit Singh
                • 5. Re: Reclaiming space from LOB
                  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.
                  1 person found this helpful
                  • 6. Re: Reclaiming space from LOB
                    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$$';

                    BYTES BLOCKS
                    --------------- ----------
                    26957840384 3290752

                    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.

                    Daljit Singh

                    Edited by: Daljit on Jun 30, 2009 1:11 PM
                    • 7. Re: Reclaiming space from LOB
                      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.

                      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.
                      • 8. Re: Reclaiming space from LOB
                        Try this

                        ALTER TABLE test MOVE LOB(image) STORE AS ( TABLESPACE lob_dat1 )

                        This will help you to reclaim the space.

                        • 9. Re: Reclaiming space from LOB
                          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 moreover shrink will create a huge amount of redo and undo which need more space and manageability and it make it tooooo slow.

                          Daljit Singh

                          Edited by: Daljit on Jun 30, 2009 4:03 PM
                          • 10. Re: Reclaiming space from LOB
                            Please if someone can help me to solve this...
                            • 11. Re: Reclaiming space from LOB
                              Daljit Pra Ji,

                              Use the command saravanan gave you.

                              In lieu of that you can create a staging table, move the rows truncate the partition and re-insert;

                              Good Luck!
                              • 12. Re: Reclaiming space from LOB
                                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.
                                • 13. Re: Reclaiming space from LOB
                                  Hi Deljit, I see this on my 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.
                                  • 14. Re: Reclaiming space from LOB
                                    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.

                                    Daljit Singh

                                    Edited by: Daljit on Jul 1, 2009 8:01 AM