It has been an ongoing complaint that the amount of space taken up within a database isn't always reported appropriately and that even though records are deleted, that freed space isn't realized in expected proportions; if 2k worth of data is deleted, there should be a 2k worth of space available.
Space management in an Oracle database is complicated by Oracle's usage of blocks and tracking through a "freelist".
In Oracle's scheme of things, data is stored at the atomic-level within blocks. While Oracle now allows database to be created with mixed sizes of block, typically blocks are the same size and by default that is 8 kilobytes.
Oracle manages block usage through a "freelist", conceptually a file that has a list of all blocks and if they are available for use (they have space) or not.
Oracle further manages blocks through the use of two thresholds: an upper threshold and a low threshold. As Oracle needs to store date, it checks the freelist, identifies the next available block with space, determines if it has enough space, and then uses it. Once that block "fills" past, for example, 90%, and upper threshold, Oracle updates the freelist to show that block as being filled and doesn't consider it for use until it is emptied. If a record is deleted and it has been stored in a block that contains other records, the block may remain unavailable according to the freelist until the block is, for example, less than 20% full, a lower threshold.
While this makes storage management nice and zippy for Oracle, it causes customers to tear out their hair. Add to this the fact that Oracle uses the freelist to calculate free space. Iimagine 10 blocks all full, so off the freelist, then half the records emptied from each block; you may think there is 40k free, but Oracle, according to it's free list sees 80k full.
No wonder it's so confusing to explain where a customer's space went.
While the following scripts aren't a panacea, they do provide evidence of how Oracle manages storage and gives people a way to understand what's happening at the block level.
All scripts need to be run from SQLPlus logged in as sys as sysdba.
The first script generates a list of "segments" or objects with in a tablespace. These are necessary for the subsequent scripts. The script example is set to gather information on the LOB_TABLESPACE owned by user PROTECT:
select segment_name, segment_type, bytes from dba_segments where tablespace_name='LOB_TABLESPACE' and owner ='PROTECT';
begin dbms_space.space_usage ( 'PROTECT', 'MESSAGE', 'TABLE', v_unformatted_blocks, v_unformatted_bytes, v_fs1_blocks, v_fs1_bytes, v_fs2_blocks, v_fs2_bytes, v_fs3_blocks, v_fs3_bytes, v_fs4_blocks, v_fs4_bytes, v_full_blocks, v_full_bytes); dbms_output.put_line('Unformatted Blocks = '||v_unformatted_blocks); dbms_output.put_line('Blocks with 00-25% free space = '||v_fs1_blocks); dbms_output.put_line('Blocks with 26-50% free space = '||v_fs2_blocks); dbms_output.put_line('Blocks with 51-75% free space = '||v_fs3_blocks); dbms_output.put_line('Blocks with 76-100% free space = '||v_fs4_blocks); dbms_output.put_line('Full Blocks = '||v_full_blocks); end; /
Unformatted Blocks = 48 Blocks with 00-25% free space = 0 Blocks with 26-50% free space = 7 Blocks with 51-75% free space = 0 Blocks with 76-100% free space = 64 Full Blocks = 6173
PL/SQL procedure successfully completed.
CONFUSION ALERT! Take a moment to reflect on the two examples above. In the output from the first example, where "SYS_LOB0000046323C00003$$", a lob segment, was investigated showed:
Blocks with 00-25% free space = 0 Blocks with 26-50% free space = 0 Blocks with 51-75% free space = 0 Blocks with 76-100% free space = 0 Full Blocks = 377663
while the output for the MESSAGE table showed:
Blocks with 00-25% free space = 0 Blocks with 26-50% free space = 7 Blocks with 51-75% free space = 0 Blocks with 76-100% free space = 64 Full Blocks = 6173
Either the LOB segments are so well organized as to fit squarely in each block or there really aren't any fractionally filled blocks. And the answer to both is, strangely, "Yes". LOB segments group blocks together into "chunks". The blocks inside the chunks appear to be either used or empty. While the output is confusing, it's also accurate. In regular segment blocks all the information is drawn from the block header. In a LOB segment "chunk" this information is drawn from a chunk header. LOB blocks don't appear as fractionally filled, either their blocks are fully occupied or they aren't.
Hopefully, this has provided another manner in which to explain to a customer how their data is being stored.
Imported Document ID: TECH221784
Subscribing will provide email updates when this Article is updated. Login is required.