Oracle tool: show_space

Create or replace procedure show_space
(P_segname_1 in varchar2,
P_space in varchar2 default 'manual ',
P_type_1 in varchar2 default 'table ',
P_analyzed in varchar2 default 'n ',
P_owner_1 in varchar2 Default User)
As
P_segname varchar2 (100 );
P_type varchar2 (10 );
P_owner varchar2 (30 );

Rochelle unformatted_blocks number;
Rochelle unformatted_bytes number;
Rochelle fs1_blocks number;
Rochelle fs1_bytes number;
Rochelle fs2_blocks number;
Rochelle fs2_bytes number;
Rochelle fs3_blocks number;
Rochelle fs3_bytes number;
Rochelle fs4_blocks number;
Rochelle fs4_bytes number;
Rochelle Blocks number;
Rochelle bytes number;

Rochelle free_blks number;
Rochelle total_blocks number;
Rochelle total_bytes number;
Rochelle unused_blocks number;
Rochelle unused_bytes number;
Rochelle lastusedextfileid 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_segname: = upper (p_segname_1); -- rainy changed
P_owner: = upper (p_owner_1 );
P_type: = p_type_1;

If (p_type_1 = 'I' or p_type_1 = 'I') then -- rainy changed
P_type: = 'index ';
End if;

If (p_type_1 = 'T' or p_type_1 = 'T') then -- rainy changed
P_type: = 'table ';
End if;

If (p_type_1 = 'C' or p_type_1 = 'C') then -- rainy changed
P_type: = 'cluster ';
End if;

Dbms_space.unused_space
(Segment_owner => p_owner,
Segment_name => p_segname,
Segment_type => p_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 );

If p_space = 'manual' or (p_space <> 'auto' and p_space <> 'auto') then
Dbms_space.free_blocks
(Segment_owner => p_owner,
Segment_name => p_segname,
Segment_type => p_type,
Freelist_group_id => 0,
Free_blks => l_free_blks );

P ('free blocks ', l_free_blks );
End if;

P ('total blocks ', l_total_blocks );
P ('total bytes ', l_total_bytes );
P ('unused blocks ', l_unused_blocks );
P ('unused bytes ', l_unused_bytes );
P ('Last used ext fileid', l_lastusedextfileid );
P ('Last used ext blockid', l_lastusedextblockid );
P ('Last used Block', l_last_used_block );

/* If the segment is analyzed */
If p_analyzed = 'y' then
Dbms_space.space_usage (segment_owner => p_owner,
Segment_name => p_segname,
Segment_type => p_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_blocks,
Fs3_bytes => l_fs3_bytes,
Fs4_blocks => l_fs4_blocks,
Fs4_bytes => l_fs4_bytes,
Full_blocks => l_full_blocks,
Full_bytes => l_full_bytes );
Dbms_output.put_line (rpad ('', 50 ,'*'));
Dbms_output.put_line ('the segment is analyzed ');
P ('0% -- 25% free space blocks ', l_fs1_blocks );
P ('0% -- 25% free space bytes ', l_fs1_bytes );
P ('1970 -- 25% free space blocks ', l_fs2_blocks );
P ('1970 -- 25% free space bytes ', l_fs2_bytes );
P ('1970 -- 50% free space blocks ', l_fs3_blocks );
P ('1970 -- 50% free space bytes ', l_fs3_bytes );
P ('1970 -- 75% free space blocks ', l_fs4_blocks );
P ('1970 -- 75% free space bytes ', l_fs4_bytes );
P ('unused blocks ', l_unformatted_blocks );
P ('unused bytes ', l_unformatted_bytes );
P ('total blocks ', l_full_blocks );
P ('total bytes ', l_full_bytes );

End if;

End;

Assm tables

SQL> exec show_space ('T', 'auto ');
Total blocks ......
Total Bytes...
Unused blocks ......
Unused bytes...
Last used ext fileid...
Last used ext blockid... 25608
Last used block...

PL/SQL procedure successfully completed.

Assm-type index

SQL> exec show_space ('t_ Index', 'auto', 'I ');
Total blocks ......
Total Bytes...
Unused blocks ......
Unused bytes...
Last used ext fileid...
Last used ext blockid... 25312
Last used block...

PL/SQL procedure successfully completed.

You can perform the segment operation on analyze.

SQL> exec show_space ('T', 'auto', 't', 'y ');
Total blocks ......
Total Bytes...
Unused blocks ......
Unused bytes...
Last used ext fileid...
Last used ext blockid... 25608
Last used block...
**************************************** *********
The segment is analyzed
0% -- 25% free space blocks ...... 0
0% -- 25% free space bytes ...... 0
25% -- 50% free space blocks ...... 0
25% -- 50% free space bytes ...... 0
50% -- 75% free space blocks ...... 0
50% -- 75% free space bytes ...... 0
75% -- 100% free space blocks ...... 0
75% -- 100% free space bytes ...... 0
Unused blocks ......
Unused bytes...
Total blocks ......
Total Bytes...

PL/SQL procedure successfully completed.