Oracle: is it possible to calculate actual storage size used by the data in a table?

Question

I want to be able to report on the actual storage volume being used by data within a table, so that if for example I then remove n. rows of data (as part of a Data Deletion piece of work), I can then re-analyse the table storage volume to calculate the actual volume of storage that has been freed up by the data removal.

I'm using the following query, but have realised that this is probably returning the allocated storage space to individual table segments, rather than the actual storage space that is being consumed by the data:

SELECT 
     OWNER "SCHEMA"
    ,SEGMENT_NAME 
    ,SUM(BYTES) "TOTAL BYTES"
    ,SUM(BYTES/1024) "TOTAL KB (BINARY)"
    ,SUM((BYTES/1024)/1024) "TOTAL MB (BINARY)"
    ,ROUND(SUM(((BYTES/1024)/1024)/1024),6) "TOTAL GB (BINARY)" 
FROM dba_segments
WHERE 
    SEGMENT_TYPE = 'TABLE'
GROUP BY 
     OWNER
    ,SEGMENT_NAME
ORDER BY 
     OWNER 
    ,ROUND (SUM(((BYTES/1024)/1024)/1024),6) desc

The Oracle Database Concepts documentation is really useful, but reading through some of the other documentation available for the various static data dictionary views, it's not immediately clear to me where/what is the best basis for making the calculation I need.

For example, if I take the stat from the 'BLOCKS' field in the DBA_TABLES view, and multiply this by our allocated block size (i.e. 8 bytes), is that giving me the allocated or actual data volume for the table?

Answer 1

You probably want something based on the dbms_space.space_usage procedure. This will tell you how many blocks are 0-25% full, 25-50% full, 50-75% full, and 75-100% full, and completely full. I use the midpoint estimate in the script below-- it may make more sense for you to use an optimistic or a pessimistic estimate if you are trying to determine how much free space you are going to create by removing a lot of data. You'd also need to run the same procedure for any indexes, materialized views, etc. that would be impacted by removing data from the table.

drop table foo;
/

create table foo (
  col1 number
);

insert into foo
  select level 
    from dual
 connect by level <= 10000;
 
declare
  l_unformatted_blocks integer;
  l_unformatted_bytes integer;
  l_fs1_blocks integer;
  l_fs1_bytes integer;
  l_fs2_blocks integer;
  l_fs2_bytes integer;
  l_fs3_blocks integer;
  l_fs3_bytes integer;
  l_fs4_blocks integer;
  l_fs4_bytes integer;
  l_full_blocks integer;
  l_full_bytes integer;
  l_partition_name varchar2(30) := null;
  l_segment_size integer;
begin
  select sum(bytes)
    into l_segment_size
    from user_segments
   where segment_name = 'FOO';

  dbms_space.space_usage( user, 'FOO', 'TABLE',
                          l_unformatted_blocks, l_unformatted_bytes,
                          l_fs1_blocks, l_fs1_bytes,
                          l_fs2_blocks, l_fs2_bytes,
                          l_fs3_blocks, l_fs3_bytes,
                          l_fs4_blocks, l_fs4_bytes,
                          l_full_blocks, l_full_bytes,
                          l_partition_name );
  dbms_output.put_line( 'Segment size is ' || l_segment_size || 
                        ' of which roughly ' || 
                        to_char( 
                          l_full_bytes + 
                          l_fs4_bytes * 0.125 + 
                          l_fs3_bytes * 0.375 + 
                          l_fs2_bytes * 0.625 +
                          l_fs1_bytes * 0.875
                        ) ||
                        ' bytes used ' ||
                        to_char( 
                          8 * 1024 *
                          l_full_blocks + 
                          l_fs4_blocks * 0.125 + 
                          l_fs3_blocks * 0.375 + 
                          l_fs2_blocks * 0.625 +
                          l_fs1_blocks * 0.875
                        ) ||
                        ' if we use blocks.' 
);
end;
/