Cacher is the code snippet organizer for pro developers

connect &your_account/&your_account_password
 
create table segment_space_stats
       (owner                     varchar2(32),
        segment_name              varchar2(32),
        segment_type              varchar2(32),
        tablespace_name           varchar2(32),
        segment_space_management  varchar2(32),
        unformatted_blocks        number,
        unformatted_bytes         number,
        fs1_blocks                number,
        fs1_bytes                 number,
        fs2_blocks                number,
        fs2_bytes                 number,
        fs3_blocks                number,
        fs3_bytes                 number,
        fs4_blocks                number,
        fs4_bytes                 number,
        full_blocks               number,
        full_bytes                number,
        total_blocks              number,
        total_bytes               number,
        unused_blocks             number,
        unused_bytes              number,
        last_used_extent_file_id  number,
        last_used_extent_block_id number,
        last_used_block           number,
        timestamp                 date)
;

-- You may need to grant these to your_account.
--
-- connect sys as sysdba
-- grant select any tables, select any dictionary, analyze any to &your_account;
-- grant execute on dbms_space to &your_account;
-- connect &your_account/&your_account_password
 
CREATE OR REPLACE PROCEDURE GEN_SEGMENT_SPACE_STATS 
AS
  index_does_not_exist EXCEPTION;
  table_does_not_exist EXCEPTION;
  PRAGMA EXCEPTION_INIT(index_does_not_exist, -1418);
  PRAGMA EXCEPTION_INIT(table_does_not_exist, -942);

  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;

  v_total_blocks number;
  v_total_bytes number;
  v_unused_blocks number;
  v_unused_bytes number;
  v_last_used_extent_file_id number;
  v_last_used_extent_block_id number;
  v_last_used_block number;

BEGIN
  for s in (select owner, segment_name, replace(segment_type,'LOBSEGMENT','LOB') segment_type, seg.tablespace_name, segment_space_management
              from dba_tablespaces ts, dba_segments seg
             where ts.tablespace_name not in ('SYSTEM', 'SYSAUX')
               and ts.tablespace_name = seg.tablespace_name
               and segment_type in ('TABLE','INDEX','CLUSTER','LOB','LOBSEGMENT'))
  loop
    begin

      if s.segment_space_management = 'AUTO' then
         dbms_space.space_usage(s.owner, s.segment_name, s.segment_type, 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 , NULL);
      end if;
      dbms_space.unused_space(s.owner, s.segment_name, s.segment_type, v_total_blocks, v_total_bytes,
                              v_unused_blocks, v_unused_bytes, v_last_used_extent_file_id,
                              v_last_used_extent_block_id, v_last_used_block, NULL);
                              
      merge into segment_space_stats r
           using (select s.owner as owner, s.segment_name as segment_name, s.segment_type as segment_type from dual) t
              on (r.owner = t.owner and r.segment_name = t.segment_name and r.segment_type = t.segment_type)
            when matched then update
             set r.tablespace_name = s.tablespace_name, r.unformatted_blocks = v_unformatted_blocks,
                 r.unformatted_bytes = v_unformatted_bytes, r.fs1_blocks = v_fs1_blocks, r.fs1_bytes = v_fs1_bytes,
                 r.fs2_blocks = v_fs2_blocks, r.fs2_bytes = v_fs2_bytes, r.fs3_blocks = v_fs3_blocks,
                 r.fs3_bytes = v_fs3_bytes, r.fs4_blocks = v_fs4_blocks, r.fs4_bytes = v_fs4_bytes,
                 r.full_blocks = v_full_blocks, r.full_bytes = v_full_bytes, r.total_blocks = v_total_blocks,
                 r.total_bytes = v_total_bytes, r.unused_blocks = v_unused_blocks, r.unused_bytes = v_unused_bytes,
                 r.last_used_extent_file_id = v_last_used_extent_file_id, r.last_used_extent_block_id =v_last_used_extent_block_id,
                 r.last_used_block = v_last_used_block, r.timestamp = sysdate
            when not matched then insert
          values (s.owner, s.segment_name, s.segment_type, s.tablespace_name, s.segment_space_management,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, v_total_blocks, v_total_bytes,
                  v_unused_blocks, v_unused_bytes, v_last_used_extent_file_id, v_last_used_extent_block_id,
                  v_last_used_block,sysdate);
         
    exception
      when index_does_not_exist then
        null; -- ignore these errors
      when table_does_not_exist then
        null; -- ignore these errors
      when others then
        raise;
    end;
  end loop;
  commit;

  delete segment_space_stats where (owner, segment_name, segment_type, tablespace_name) not in
  (select owner, segment_name, replace(segment_type,'LOBSEGMENT','LOB'), tablespace_name from dba_segments
    where tablespace_name not in ('SYSTEM', 'SYSAUX')
      and segment_type in ('TABLE','INDEX','CLUSTER','LOB','LOBSEGMENT'));
  commit;
END;
/

TTITLE center underline "Top Ten Largest Space Wasters"
set linesize 140 pagesize 20
col owner for a20
col segment_type for a12
col total_MB for 999999
col unformatted_MB for 999999
col unused_MB for 999999
col pct_unused for 999 heading "Pct Unused"
col estmtd_pssbl_svngs for 99999 heading "Estimated|Possible Savings|w Index Rebuild"
clear breaks
clear computes
break on report dup
compute sum of total_MB on report
compute sum of unused_MB on report
 
select a.*
  from (
        select owner, segment_name, segment_type,
               total_bytes/1048576 total_MB,
               unformatted_bytes/1048576 unformatted_MB,
               unused_bytes/1048576 unused_MB,
               trunc(unused_bytes / total_bytes * 100) pct_unused,
               case when segment_type = 'INDEX' and total_blocks >= 256 and fs2_blocks >= 64
                         then unused_blocks + fs2_blocks
                    else 0
               end * b.block_size/1048576 estmtd_pssbl_svngs
          from segment_space_stats a, dba_tablespaces b
         where unused_bytes <> 0
           and a.tablespace_name = b.tablespace_name
         order by unused_bytes desc) a
where rownum<11
;

TTITLE center underline "Top Ten Indexes with non-full blocks"
clear breaks
clear computes
set linesize 145 pagesize 100
col owner for a20
col fs1_blocks for 9999999 heading "< 25%|blocks free"
col fs2_blocks for 9999999 heading "25-50%|blocks free"
col fs3_blocks for 9999999 heading "50-75%|blocks free"
col fs4_blocks for 9999999 heading "75-100%|blocks free"
col full_blocks for 9999999 heading "blocks|full"
col total_blocks for 9999999 heading "total|blocks"
col estmtd_pssbl_svngs for 99999 heading "Estimated|Possible Savings|w Index Rebuild"
col block_size noprint
break on report dup
compute sum of estmtd_pssbl_svngs on report
select a.*
  from (
        select owner, segment_name,
               fs1_blocks ,
               fs2_blocks ,
               fs3_blocks ,
               fs4_blocks ,
               full_blocks, total_blocks,
               case when segment_type = 'INDEX' and total_blocks >= 256 and fs2_blocks >= 64
                         then unused_blocks + fs2_blocks
                    else 0
               end * b.block_size/1048576 estmtd_pssbl_svngs,
               b.block_size
          from segment_space_stats a, dba_tablespaces b
         where segment_type = 'INDEX'
           and a.tablespace_name = b.tablespace_name
         order by fs4_blocks desc, fs3_blocks desc, fs2_blocks desc, fs1_blocks desc) a
where rownum<11
;

TTITLE center underline "Top Ten Largest Empty Segments"
set linesize 140 pagesize 100
col owner for a20
col total_MB for 999999
clear breaks
clear computes
break on report dup
compute sum of total_MB on report
select a.*
  from (select owner, segment_name, segment_type, tablespace_name, total_bytes/1048576 total_MB
          from segment_space_stats
         where full_bytes = 0
        order by total_bytes desc) a
where rownum<11
;