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

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,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( '25% -- 50% free space blocks', l_fs2_blocks); 
p( '25% -- 50% free space bytes', l_fs2_bytes); 
p( '50% -- 75% free space blocks', l_fs3_blocks); 
p( '50% -- 75% free space bytes', l_fs3_bytes); 
p( '75% -- 100% free space blocks', l_fs4_blocks); 
p( '75% -- 100% 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 类型的 表 

SQL> exec show_space('t','auto'); 
Total Blocks............................512 
Total Bytes.............................4194304 
Unused Blocks...........................78 
Unused Bytes............................638976 
Last Used Ext FileId....................9 
Last Used Ext BlockId...................25608 
Last Used Block.........................50 

PL/SQL procedure successfully completed. 


ASSM 类型的索引 


SQL> exec show_space('t_index','auto','i'); 
Total Blocks............................80 
Total Bytes.............................655360 
Unused Blocks...........................5 
Unused Bytes............................40960 
Last Used Ext FileId....................9 
Last Used Ext BlockId...................25312 
Last Used Block.........................3 

PL/SQL procedure successfully completed. 


对analyze 过的segment 可以这样 

SQL> exec show_space('t','auto','T','Y'); 
Total Blocks............................512 
Total Bytes.............................4194304 
Unused Blocks...........................78 
Unused Bytes............................638976 
Last Used Ext FileId....................9 
Last Used Ext BlockId...................25608 
Last Used Block.........................50 
************************************************* 
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...........................0 
Unused Bytes............................0 
Total Blocks............................418 
Total bytes.............................3424256 

PL/SQL procedure successfully completed.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/8570952/viewspace-713542/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/8570952/viewspace-713542/