Oracle stored procedures show_space full version

The Show_space version is available on the web for a number of editions. Today, a generic version is sorted out for later use.

Create or Replace procedure Show_space (V_segment_name in Varchar2, V_segment_owne
                                       R in varchar2 Default user, V_segment_type in varchar2 default ' TABLE ', p_analyzed in varchar2 default ' Y ', p_partitio
  N_name in varchar2 default null) as P_segment_name VARCHAR2 (30);
  P_segment_owner VARCHAR2 (30);
  P_segment_type VARCHAR2 (30);

  P_space VARCHAR2 (30);
  L_unformatted_blocks number;
  L_unformatted_bytes number;
  L_fs1_blocks number;
  L_fs1_bytes number;
  L_fs2_blocks number;
  L_fs2_bytes number;
  L_fs3_blocks number;
  L_fs3_bytes number;
  L_fs4_blocks number;
  L_fs4_bytes number;
  L_full_blocks number;

  L_full_bytes number;
  L_free_blks number;
  L_total_blocks number;
  L_total_bytes number; L_unused_blocks number;
  L_unused_bytes number;
  L_lastusedextfileid number;
  L_lastusedextblockid number;

  L_last_used_block number;
  Procedure P (P_label in varchar2, p_num in number) is Begin Dbms_output.put_line (Rpad,, '. ') | | p_label);
End
  Begin P_segment_name: = Upper (V_segment_name);
  P_segment_owner: = Upper (V_segment_owner);

  P_segment_type: = Upper (V_segment_type);
  if (P_segment_type = ' I ' or p_segment_type = ' index ') then p_segment_type: = ' index ';
  elsif (p_segment_type = ' T ' or p_segment_type = ' table ') then p_segment_type: = ' table ';
  elsif (p_segment_type = ' C ' or p_segment_type = ' CLUSTER ') then p_segment_type: = ' CLUSTER ';

  End If; Execute immediate ' Select Ts.segment_space_management from dba_segments seg, dba_tablespaces ts where seg.segment_name =:  P_segname and (:p _partition is null or seg.partition_name =:p _partition) and Seg.owner =:p _owner and Seg.tablespace_name = Ts.tablespace_name ' into p_sPace using P_segment_name, P_partition_name, P_partition_name, P_segment_owner;              Dbms_space.unused_space (Segment_owner => P_segment_owner, segment_name
                          => p_segment_name, Segment_type => P_segment_type, Total_blocks => l_total_blocks, total_bytes => l_total_bytes              , Unused_blocks => l_unused_blocks, unused_bytes
                          => l_unused_bytes, last_used_extent_file_id => L_lastusedextfileid, last_used_extent_block_id => L_lastusedextblockid, Last_used_block =&gt ;
  L_last_used_block, Partition_name => p_partition_name);
  P (' Total Blocks ', l_total_blocks);
  P (' Total Bytes ', l_total_bytes); P (' TOtal MBytes ', l_total_bytes/1024/1024);
  P (' Unused Blocks ', l_unused_blocks);
  P (' Unused Bytes ', l_unused_bytes);
  P (' Unused Kbytes ', l_unused_bytes/1024);
  P (' Used Blocks ', l_total_blocks-l_unused_blocks);
  P (' Used Bytes ', l_total_bytes-l_unused_bytes);
  P (' Used Kbytes ', (l_total_bytes-l_unused_bytes)/1024);
  P (' Last Used Ext Fileid ', L_lastusedextfileid);
  P (' Last Used Ext blockid ', l_lastusedextblockid);

  P (' last Used block ', l_last_used_block); If p_analyzed = ' Y ' then if p_space = ' AUTO ' then Dbms_space.space_usage (Segment_owner => P_segment_owne R, Segment_name => p_segment_name, Segment_type =
                             > P_segment_type, unformatted_blocks => l_unformatted_blocks,
                     Unformatted_bytes => l_unformatted_bytes, fs1_blocks => l_fs1_blocks,        Fs1_bytes => l_fs1_bytes, fs2_blocks => l_fs2_blocks, Fs2_bytes => l_fs2_bytes, fs3_blocks => L_fs3_blo CKS, Fs3_bytes => l_fs3_bytes, Fs4_blocks =& Gt        L_fs4_blocks, Fs4_bytes => l_fs4_bytes, Full_blocks
                             => l_full_blocks, full_bytes => l_full_bytes,
    
      Partition_name => p_partition_name);
      Dbms_output.put_line (");
      Dbms_output.put_line (' The segment is analyzed below ');
      P (' FS1 Blocks (0-25) ', l_fs1_blocks);
      P (' FS2 Blocks (25-50) ', l_fs2_blocks);
      P (' FS3 Blocks (50-75) ', l_fs3_blocks);
      P (' FS4 Blocks (75-100) ', l_fs4_blocks); P (' Unformatted Blocks ', l_unformatted_blocks);
    P (' Full Blocks ', l_full_blocks);      else Dbms_space.free_blocks (Segment_owner => P_segment_owner, segment_name => p_segment_name, Segment_type => P_segment_type, FR
      eelist_group_id => 0, free_blks => l_free_blks);
    P (' Free Blocks ', l_free_blks);
  End If;
End If; End