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 => ;
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