By Franck Pachot

.
You have the feeling that your table takes more blocks than it should? Here are the queries I use to quickly check the free space. The idea is to call DBMS_SPACE.SPACE_USAGE and infer the minimum space from the percentages. For example, a block in FS3 (defined as having at least 50 to 75% free space) is supposed to have at least 50% of free space. Of course it can have more, but you don’t know.

Here is some PL/SQL to do so:


set serveroutput on
declare
 unf number; unfb number; fs1 number; fs1b number; fs2 number; fs2b number; fs3 number; fs3b number; fs4 number; fs4b number; full number; fullb number; 
begin
 for i in (select * from (select * from dba_segments where segment_subtype='ASSM' and segment_type in (
  'TABLE','TABLE PARTITION','TABLE SUBPARTITION','CLUSTER','LOB','LOB PARTITION','LOB SUBPARTITION'
 ) order by bytes desc) where 10>=rownum)
 loop
  begin
   dbms_space.space_usage(i.owner,i.segment_name,i.segment_type,unf,unfb,fs1,fs1b,fs2,fs2b,fs3,fs3b,fs4,fs4b,full,fullb,partition_name=>i.partition_name);
   dbms_output.put_line(to_char((unfb+fs1b+fs2b*0.25+fs3b*0.5+fs4b*0.75)/1024/1024/1024,'999G999D999')||' GB free in '||i.segment_type||' "'||i.owner||'"."'||i.segment_name||'" partition "'||i.partition_name||'"');
  exception
   when others then dbms_output.put_line(i.segment_type||' "'||i.owner||'"."'||i.segment_name||'" partition "'||i.partition_name||'": '||sqlerrm);
  end; 
 end loop;
end;
/

The output looks like:


        .001 GB free in INDEX "DEMO"."ACCOUNT_PK" partition ""
        .001 GB free in TABLE "APEX_040200"."WWV_FLOW_PAGE_PLUGS" partition ""
        .009 GB free in TABLE "SCOTT"."DEMO" partition ""
        .000 GB free in TABLE "APEX_040200"."WWV_FLOW_STEP_ITEMS" partition ""
        .003 GB free in INDEX "SYS"."WRH$_SYSMETRIC_HISTORY_INDEX" partition ""
        .000 GB free in TABLE "MDSYS"."SDO_CS_SRS" partition ""
        .002 GB free in INDEX "SYS"."I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST" partition ""
        .006 GB free in TABLE "SYS"."WRH$_SYSMETRIC_HISTORY" partition ""
        .002 GB free in TABLE "SYS"."WRH$_SQL_PLAN" partition ""

If you are in 12c, an inline function in the query might come handy:


with function freebytes(segment_owner varchar2, segment_name varchar2, segment_type varchar2,partition_name varchar2) return number as
 unf number; unfb number; fs1 number; fs1b number; fs2 number; fs2b number; fs3 number; fs3b number; fs4 number; fs4b number; full number; fullb number; 
begin
 dbms_space.space_usage(segment_owner,segment_name,segment_type,unf,unfb,fs1,fs1b,fs2,fs2b,fs3,fs3b,fs4,fs4b,full,fullb,partition_name=>partition_name);
 return unfb+fs1b+fs2b*0.25+fs3b*0.5+fs4b*0.75;
end;
select round(freebytes(owner,segment_name,segment_type,partition_name)/1024/1024/1024,3) free_GB,segment_type,owner,segment_name,partition_name
from dba_segments  where segment_subtype='ASSM' and segment_type in (
  'TABLE','TABLE PARTITION','TABLE SUBPARTITION','CLUSTER','LOB','LOB PARTITION','LOB SUBPARTITION'
) order by bytes desc fetch first 10 rows only
/

The result looks like:


FREE_GB SEGMENT_TYPE   OWNER         SEGMENT_NAME                     PARTITION_NAME
------- ------------   -----         ------------                     --------------
      0 TABLE          DEMO          ACCOUNTS
  0.001 INDEX          DEMO          ACCOUNT_PK
  0.001 TABLE          APEX_040200   WWV_FLOW_PAGE_PLUGS
  0.009 TABLE          SCOTT         DEMO
  0.003 INDEX          SYS           WRH$_SYSMETRIC_HISTORY_INDEX
      0 TABLE          APEX_040200   WWV_FLOW_STEP_ITEMS
  0.002 INDEX          SYS           I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST
      0 TABLE          MDSYS         SDO_CS_SRS
  0.006 TABLE          SYS           WRH$_SYSMETRIC_HISTORY
  0.002 TABLE          SYS           WRH$_SQL_PLAN

Future evolution will be published on GitHub:
https://raw.githubusercontent.com/FranckPachot/scripts/master/administration/segment_free_space_plsql.sql
https://raw.githubusercontent.com/FranckPachot/scripts/master/administration/segment_free_space_sql.sql

Note that having free space does not mean that you have to shrink or reorg. Try to understand what happened to your data before, and whether this space will be reused soon.

Update 8-AUG-2018

In the initial post I added all segment types accepted by the dbms_space documentation but finally removed ‘INDEX’,’INDEX PARTITION’,’INDEX SUBPARTITION’ because the meaning of the output is completely different. See Jonathan Lewis note about it: https://jonathanlewis.wordpress.com/2013/12/17/dbms_space_usage/