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';
The output looks like this:
SEGMENT_NAME
---------------------------------------------------------------------------------
SEGMENT_TYPE BYTES
------------------ ----------
SYS_IL0000046323C00003$$
LOBINDEX 65536
SYS_IL0000046265C00003$$
LOBINDEX 65536
SYS_IL0000046279C00004$$
LOBINDEX 65536
SYS_LOB0000046323C00003$$
LOBSEGMENT 3154116608
SYS_LOB0000046265C00003$$
LOBSEGMENT 65536
SYS_LOB0000046279C00004$$
LOBSEGMENT 17825792
The next script will display the block counts according to amount of freespace. This script focuses on lobsegment 'SYS_LOB0000046323C00003$$':
set serveroutput on
declare
v_unformatted_blocks number;
v_unformatted_bytes number;
v_fs1_blocks number;
v_fs1_bytes number;
v_fs2_blocks number;
v_fs2_bytes number;
v_fs3_blocks number;
v_fs3_bytes number;
v_fs4_blocks number;
v_fs4_bytes number;
v_full_blocks number;
v_full_bytes number;
begin
dbms_space.space_usage ('PROTECT', 'SYS_LOB0000046323C00003$$', 'LOB', 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;
/
the output from this is:
Unformatted Blocks = 6493
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
PL/SQL procedure successfully completed.
From a table perspective, the following script can be used. This script focuses on MESSAGE table:
set serveroutput on
declare
v_unformatted_blocks number;
v_unformatted_bytes number;
v_fs1_blocks number;
v_fs1_bytes number;
v_fs2_blocks number;
v_fs2_bytes number;
v_fs3_blocks number;
v_fs3_bytes number;
v_fs4_blocks number;
v_fs4_bytes number;
v_full_blocks number;
v_full_bytes number;
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.