Rem Filename   : tsSpace.sql
Rem 
Rem Parameters : &1 = Tablespace_name
Rem
Rem Author     : 
Rem RDBMS      : Oracle V8.x, v9.x
Rem
Rem
Rem Sample output
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
SET TERM ON


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 InputTS     = ' '
DEFINE DefaultStorageEfficiency = 80

COLUMN ttss NOPRINT NEW_VALUE InputTS

SELECT '&&1' ttss from dual;

CLEAR COLUMNS

@@header_dbinfo 107 DC

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;
  TSnum                     number := 0;
  TSTotalSize               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 );


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 s.tablespace_name      =    UPPER('&InputTS')
                -- 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
      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 );

    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;

  end loop;

  SELECT nvl(sum(bytes)/1024,0) 
    INTO TotalIndexSize
    FROM dba_indexes i, dba_segments s
   WHERE s.tablespace_name      =    UPPER('&InputTS')
     AND i.index_name   = s.segment_name
     AND i.owner        = s.owner;

      OccupiedByOthers := TotalSize - ( (TotalDataBytes/1024) + 
                                        (TotalFreeListSpace/1024) +
                                        (TotalUnusedBlocks*v_block_size_Kbytes) );

      SummaryTitle := 'Data segment summary for tablespace <&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/1024,'9G999G999') || ' Mbytes   ' 
                         || to_char(case TotalSize when 0 then 0 else (TotalDataBytes/1024)*(100/TotalSize) end,'990D99') || ' %' );
      dbms_output.put_line( 'Total storage overhead                     : ' 
                         || to_char(OccupiedByOthers/1024,'9G999G999') || ' Mbytes   ' 
                         || to_char(case TotalSize when 0 then 0 else OccupiedByOthers*(100/TotalSize) end,'990D99') || ' %' );
      dbms_output.put_line( 'Total free space for inserts below HWM     : ' 
                         || to_char(TotalFreeListSpace/1024/1024,'9G999G999') || ' Mbytes   ' 
                         || to_char(case TotalSize when 0 then 0 else (TotalFreeListSpace/1024)*(100/TotalSize) end,'990D99') || ' %' );
      dbms_output.put_line( 'Total free space above high water marks    : '
                         || to_char(TotalUnusedBlocks*v_block_size_Kbytes/1024,'9G999G999') || ' Mbytes   ' 
                         || to_char(case TotalSize when 0 then 0 else TotalUnusedBlocks*v_block_size_Kbytes*100/TotalSize end,'990D99') || ' %' );
      dbms_output.put_line( '--------------------------------------------------------------------------' );
      dbms_output.put_line( 'Total allocated space (data segments       : '
                         || to_char(TotalSize/1024,'9G999G999') || ' Mbytes    100,00 %' );

      dbms_output.put_line( 'Total allocated space (index segments)     : '
                         || to_char(TotalIndexSize/1024,'9G999G999') || ' Mbytes' );
      dbms_output.put_line( '--------------------------------------------------------------------------' );
      dbms_output.put_line( 'Total allocated space                      : '
                         || to_char((TotalSize + TotalIndexSize)/1024,'9G999G999') || ' Mbytes' );

      SELECT TS# into TSnum
      FROM V$TABLESPACE 
      WHERE NAME = UPPER('&InputTS');

      select sum(bytes)/1024/1024 into TSTotalSize
      from v$datafile where ts# = TSnum;

      dbms_output.put_line( 'Total space                                : '
                         || to_char(TSTotalSize,'9G999G999') || ' Mbytes' );
end;
/

PROMPT

SET FEEDBACK  ON
SET PAGESIZE  9999

UNDEFINE InputTS
UNDEFINE DefaultStorageEfficiency
