Rem Filename   : SpaceUtilization.sql
Rem 
Rem Parameters : &1 = [owner.]table(pattern)
Rem              The default value for owner is the currently connected user.
Rem
Rem              &2 = content indicator : D      = detailed information per table
Rem                                       S      = summary information 
Rem                                       DS     = detailed + summary information
Rem               
Rem Author     : 
Rem RDBMS      : Oracle V8.x, v9.x
Rem
Rem Modification history
Rem --------------------
Rem 29-Mar-2002  Initial creation
Rem 20-Nov-2003  Completely rewritten 
Rem              - Major performance improvement
Rem              - Several formatting improvements and additional information.
Rem              - Added summary (only correct information in case of default blocksize).
Rem                To do : display correct information in case of non default blocksize.
Rem 
Rem
Rem =======================================================================================================================
Rem
Rem Description : Lists space occupied by all matching table's data segments and the unused space 
Rem               below and above the high water mark.
Rem 
Rem               Table name        : The name of the table.
Rem               Statistics        : Shows the date when the table statistics were most recently 
Rem                                   computed or estimated, and the number of rows in the
Rem                                   table at that moment.
Rem               Segment size      : The total number of Kbytes occupied by the segment.
Rem               Used by data      : The total space occupied by real row data (without any overhead).
Rem               Free list space   : The total space free in all free list blocks.
Rem                                   Take note that free list blocks might be partially filled.
Rem               Free above HWM    : The space occupied by the blocks in the segment above the free water mark.
Rem               Overhead + free   : The space occupied by :
Rem                                    - the segment header
Rem                                    - the block headers of all blocks below the high water mark
Rem                                    - the free space in blocks containing data but which is not available for 
Rem                                      further insert because the blocks are not on one of the free lists,
Rem                                      in other words for which % occupied by data has not yet dropped below pctused.
Rem                                      This space is available for updates in existing rows within the blocks.
Rem
Rem               REMARK : In some cases (outdated statistics) the data listed might look contradictory, this
Rem                        is because some but not all columns are obtained from the table statistics.
Rem
Rem Sample output
Rem -------------
Rem MXXXXXXX<TBAR2>  @unused crossworlds.%
Rem 
Rem .                                    Statistics             Segment   Used by    Free list  Free above  Overhead + free
Rem Table name                        Row count (date)        Size (Kb)  Data (%)   Space (Kb)    HWM (Kb) above %used (Kb)
Rem ----------------------------- ------------------------- ----------- --------- ------------ ----------- ----------------
Rem ..
Rem CXREPOSBLOBS                              4 (20-NOV-03)         256      31 %           35          88         54 (21%)
Rem CXREPOSBMCOMP                             0 (20-NOV-03)         128       0 %            0         120          8 (6%)
Rem CXREPOSBOSPECATTRS                       37 (20-NOV-03)         128       3 %            4         112          8 (6%)
Rem CXREPOSBUSOBJREFS                         4 (20-NOV-03)         128       0 %            8         112          8 (6%)
Rem ..
Rem 
Rem Data segment summary for <crossworlds.%>
Rem ========================================
Rem Total space occupied by table data         :         90 Kbytes      1,34 %
Rem Total storage overhead                     :        456 Kbytes      6,85 %
Rem Total free space for inserts below HWM     :        878 Kbytes     13,20 %
Rem Total free space above high water marks    :      5.232 Kbytes     78,61 %
Rem --------------------------------------------------------------------------
Rem Total allocated space (data blocks)        :      6.656 Kbytes    100,00 %
Rem 
Rem =======================================================================================================================


SET PAGESIZE     0
SET TRIMSPOOL    ON
SET VERIFY       OFF
SET SERVEROUTPUT ON SIZE 500000 FORMAT WORD_WRAPPED
SET FEEDBACK     OFF

alter session set nls_numeric_characters = ',.';

-- Disect the input argument, and get the owner name and table name into two seperate 
-- substitution variables. The owner name defaults to the current user.

DEFINE InputOwner     = ' '
DEFINE InputTableName = ' '
DEFINE DefaultStorageEfficiency = 80

COLUMN table_owner NOPRINT NEW_VALUE InputOwner
COLUMN table_name  NOPRINT NEW_VALUE InputTableNameFilter

SELECT decode(instr('&&1','.'), 0, user,  /*Default to current user.*/
                                upper(substr('&&1',1,instr('&&1','.')-1))) table_owner,
       decode(instr('&&1','.'), 0, upper('&&1'),  /*Only the table name was passed in.*/
                                upper(substr('&&1',instr('&&1','.')+1))) table_name
  FROM dual;

CLEAR COLUMNS


declare
  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;
  FreeBelowHWM              number := 0;
  TotalFreeListSpace        number := 0;
  TotalUnusedBlocks         number := 0;
  TotalSize                 number := 0;
  TotalDataBytes            number := 0;
  OccupiedByOthers          number := 0;
  WastedSpace_Kbytes        number := 0;
  deleted_index_rows        number := 0;
  TotalIndexSize            number := 0;
 
  v_block_size              number;
  v_block_size_Kbytes       number;
  no_rows                   boolean := TRUE;
  SummaryTitle              varchar2(100);

  E_segment_does_not_exist  EXCEPTION;  PRAGMA EXCEPTION_INIT(E_segment_does_not_exist, -3211);
  E_no_privilege            EXCEPTION;  PRAGMA EXCEPTION_INIT(E_no_privilege,           -1031);
  E_no_access               EXCEPTION;  PRAGMA EXCEPTION_INIT(E_no_access,              -942 );

  cursor index_info (i_table_name VARCHAR2, i_table_owner VARCHAR2) is 
    SELECT i.owner, i.index_name, i.num_rows, s.bytes/1024 size_Kbytes 
      FROM dba_indexes i, dba_segments s
     WHERE i.table_name = i_table_name
       AND i.table_owner = i_table_owner
       AND i.index_name = s.segment_name
       AND i.owner = s.owner;

begin
  SELECT value,value/1024 INTO v_block_size,v_block_size_Kbytes FROM v$parameter WHERE name = 'db_block_size';

  for t in ( SELECT t.owner, 
                    t.table_name, 
                    t.iot_type, 
                    decode(t.last_analyzed,null,'          Never analyzed',
                           to_char(t.num_rows,'999G999G999') || ' (' ||
                           to_char(t.last_analyzed,'DD-MON-YY') || ')' )                                  RowCountStats,
                    decode(t.last_analyzed,null,'         =?=',
                           to_char(t.AVG_SPACE_FREELIST_BLOCKS*t.num_freelist_blocks/1024,'999G999G999')) FreeListSpace,
                    nvl(t.AVG_SPACE_FREELIST_BLOCKS*t.num_freelist_blocks,0)                              FreeListBytes,
                    decode(t.last_analyzed,null,'      =?=', '    ' 
                             || to_char(100*t.num_rows*t.avg_row_len/s.bytes,'99')||' %')                 RealData,
                    -- Let's use -1 for "no statistics available"
                    nvl(t.num_rows*t.avg_row_len,-1)                                                      RealDataBytes,
                    s.blocks,
                    t.empty_blocks        
               FROM dba_tables   t, 
                    dba_segments s
              WHERE t.owner      =    '&InputOwner'
                AND t.table_name like '&InputTableNameFilter'
                -- exclude temporary tables but include index organized tables
                AND ( s.tablespace_name is not null OR t.iot_type = 'IOT' )
                AND t.owner = s.owner
                AND t.table_name = s.segment_name
           ORDER BY t.owner, t.table_name )
  loop
    if no_rows then
      if ( instr( '&2', 'D' ) > 0 ) then
        dbms_output.put_line( '.                                    Statistics             Segment   Used by    Free list  Free above  Overhead + free' );
        dbms_output.put_line( 'Table name                        Row count (date)        Size (Kb)  Data (%)   Space (Kb)    HWM (Kb) above %used (Kb)' );
        dbms_output.put_line( '----------------------------- ------------------------- ----------- --------- ------------ ----------- ----------------' );
      end if;
      no_rows := FALSE;
    end if;

    -- ---------------------------------------------------------------------------------------------------------
    -- Collect information about the data segment
    -- ---------------------------------------------------------------------------------------------------------

    -- Some information can be retreived by the next call that is not available in dba_tables unless the table was
    -- analyzed. The next call is causing nearly no system load compared with using dbms_stats.
    begin
      begin
        dbms_space.unused_space( t.owner, t.table_name, 'TABLE', total_blocks, total_bytes, unused_blocks,
                                 unused_bytes, last_used_extent_file_id, last_used_extent_block_id, last_used_block
                                 -- The next paramter is optional, I put it in for future support of partitioned tables.
                                 -- , partition_name
                                );

      exception
        when E_no_privilege or E_no_access then
          -- An ORA-01031 is raised when the user is not allowed to execute the dbms_space package.
          -- An ORA-00942 is raised by dbms_space.unused_space when the user has no access to the table.
          -- Get the data from the table statistics (if these are available).
          unused_blocks := t.empty_blocks;
          total_blocks  := t.blocks;

      when others then
        dbms_output.put_line( 'The following error was trapped while processing table <' || t.table_name || '>.' );
        raise;
      end; 
       
      TotalFreeListSpace  := TotalFreeListSpace + t.FreeListBytes; 
      TotalUnusedBlocks   := TotalUnusedBlocks + nvl(unused_blocks,0);
      TotalSize           := TotalSize + total_blocks*v_block_size_Kbytes;

      if t.RealDataBytes = -1 then
        -- If no table statistics, then we only can estimate the space occupied by the real data.
        -- Without this estimation the total summaries rappidly become meaningless.
        TotalDataBytes  := TotalDataBytes + round((total_bytes-unused_bytes)*(&DefaultStorageEfficiency/100));
      else
        TotalDataBytes := TotalDataBytes + t.RealDataBytes;
      end if;

      WastedSpace_Kbytes := total_blocks*v_block_size_Kbytes 
                      - round(t.RealDataBytes/1024 + t.FreeListBytes/1024 + unused_blocks*v_block_size_Kbytes );

      if ( instr( '&2', 'D' ) > 0 ) then
        dbms_output.put_line( rpad(t.table_name,31) || t.RowCountStats || ' '
                              || to_char(total_blocks*v_block_size_Kbytes,'99G999G999') || ' '
                              || t.RealData || ' '
                              || t.FreeListSpace || ' '
                              || to_char(unused_blocks*v_block_size_Kbytes,'99G999G999') || ' '
                              || to_char(WastedSpace_Kbytes, '9G999G999') || ' ('
                              || round(WastedSpace_Kbytes*100/(total_blocks*v_block_size_Kbytes)) || '%)'
                            );     
      end if;

    exception
      when E_segment_does_not_exist then
        -- This is an index organized table, so there is no regular data segment.
        dbms_output.put_line( rpad(t.table_name,31) || t.RowCountStats || '       =iot=' );
      when others then
        raise;
    end;


/*
    -- ---------------------------------------------------------------------------------------------------------
    -- Collect information about the index segments
    -- ---------------------------------------------------------------------------------------------------------

    for i in index_info( t.table_name, t.owner )
      loop
        execute immediate 'validate index ' || i.owner || '.' || i.index_name;
   
        SELECT height, del_lf_rows, lf_rows, del_lf_rows/(lf_rows+0.00001), 
               blocks*db.block_size, round(btree_space/1024), nvl(pct_used,0)
          INTO b_tree_height, deleted_rows, index_rows, degradation, index_size, 
               btree_space, btree_pct_used
          FROM index_stats,
               (SELECT value/1024 block_size FROM v$parameter WHERE name = 'db_block_size') db;  

        SELECT del_lf_rows
          INTO deleted_index_rows
          FROM index_stats;  

        dbms_output.put_line( rpad('. ' || i.index_name,31) || rpad(to_char(i.num_rows+deleted_index_rows,'999G999G999'),25)
                              || to_char(i.size_Kbytes,'99G999G999')
                            );
      end loop;
*/
  end loop;

  SELECT sum(bytes)/1024 
    INTO TotalIndexSize
    FROM dba_indexes i, dba_segments s
   WHERE i.table_owner  =    '&InputOwner'
     AND i.table_name   like '&InputTableNameFilter'
     AND i.index_name   = s.segment_name
     AND i.owner        = s.owner;

  if no_rows then
    dbms_output.put_line( 'No matching tables found.' );
  else
    if ( instr( '&2', 'S' ) > 0 ) then
      OccupiedByOthers := TotalSize - ( (TotalDataBytes/1024) + 
                                        (TotalFreeListSpace/1024) +
                                        (TotalUnusedBlocks*v_block_size_Kbytes) );

      SummaryTitle := 'Data segment summary for <&1>';
      dbms_output.put_line( chr(10) || SummaryTitle
                         || chr(10) || rpad('=',length(SummaryTitle),'=') );
      dbms_output.put_line( 'Total space occupied by table data         : ' 
                         || to_char(TotalDataBytes/1024,'9G999G999') || ' Kbytes   ' 
                         || to_char((TotalDataBytes/1024)*(100/TotalSize),'990D99') || ' %' );
      dbms_output.put_line( 'Total storage overhead                     : ' 
                         || to_char(OccupiedByOthers,'9G999G999') || ' Kbytes   ' 
                         || to_char(OccupiedByOthers*(100/TotalSize),'990D99') || ' %' );
      dbms_output.put_line( 'Total free space for inserts below HWM     : ' 
                         || to_char(TotalFreeListSpace/1024,'9G999G999') || ' Kbytes   ' 
                         || to_char((TotalFreeListSpace/1024)*(100/TotalSize),'990D99') || ' %' );
      dbms_output.put_line( 'Total free space above high water marks    : '
                         || to_char(TotalUnusedBlocks*v_block_size_Kbytes,'9G999G999') || ' Kbytes   ' 
                         || to_char(TotalUnusedBlocks*v_block_size_Kbytes*100/TotalSize,'990D99') || ' %' );
      dbms_output.put_line( '--------------------------------------------------------------------------' );
      dbms_output.put_line( 'Total allocated space (data segments       : '
                         || to_char(TotalSize,'9G999G999') || ' Kbytes    100,00 %' );

      dbms_output.put_line( 'Total allocated space (index segments)     : '
                         || to_char(TotalIndexSize,'9G999G999') || ' Kbytes' );
    end if;
  end if;
end;
/

PROMPT

SET FEEDBACK  ON
SET PAGESIZE  9999

UNDEFINE InputOwner
UNDEFINE InputTableName 
UNDEFINE DefaultStorageEfficiency
