Full version of Oracle Stored Procedure show_space

The show_space version has many versions on the network. A general version is compiled today and will be used later.
 

Create or replace procedure show_space (v_segment_name in varchar2, Region in varchar2 Default User, v_segment_type in varchar2 default 'table', p_analyzed in varchar2 default 'y', p_partition_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); Comment number; l_fs1_blocks number; Comment number; l_fs2_blocks number; comment number; l_full_blocks number; l_full_bytes number; l_free_blks number; l_total_blocks number; Comment number; l_unused_blocks number; l_unused_bytes number; Comment number; rochelle lastusedextblockid number; Rochelle last_used_block number; Procedure P (p_label in varchar2, p_num in number) is begin dbms_output.put_line (rpad (p_label, 40 ,'. ') | p_num); end; begin p_segment_name: = upper (v_segment_name); p_segment_owner: = upper (v_segment_owner); p_segment_type: = upper (v_segment_type ); if (condition = '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, partition; partition (partition => partition, segment_name => p_segment_name, partition => partition, total_blocks => partition, partition => partition, unused_blocks => l_unused_blocks, unused_bytes => l_unused_bytes, bytes => blocks, blocks => blocks, last_used_block => blocks, partition_name => p_partition_name); P ('total blocks ', rochelle total_blocks); P ('total bytes ', Rochelle total_bytes); P ('total Mbytes', Rochelle total_bytes/1024/1024); P ('unused blocks ', Rochelle 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', bytes ); 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 (segment_owner => else, segment_name => p_segment_name, segment_type => p_segment_type, unformatted_blocks => else, blocks => blocks, fs1_blocks => blocks, fs1_bytes => blocks, fs2_blocks => blocks, fs2_bytes => blocks, fs3_blocks => blocks, fs3_bytes => blocks, fs4_blocks => blocks, fs4_bytes => blocks, full_blocks => l_full_blocks, full_bytes => l_full_bytes, partition_name => p_partition_name); blocks (''); blocks ('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 functions (functions => Functions, segment_name => p_segment_name, segment_type => Functions, freelist_group_id => 0, free_blks => l_free_blks); P ('free blocks ', l_free_blks ); end if; end;