Oracle tool: show_space Introduction

Oracle tool: show_space introduction 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); www.2cto.com comment number; Comment number; l_fs1_blocks number; l_fs1_bytes number; serial number; serial number; l_full_blocks number; l_full_bytes number; l_free_blks number; l_total_blocks number; serial 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 => blocks, unused_blocks => l_unused_blocks, unused_bytes => blocks, blocks => blocks, blocks => blocks, 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 partition (segment_owner => p_owner, segment_name => p_segname, segment_type => p_type, unformatted_blocks => blocks, 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 => 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 ('2014 -- 25% free space blocks ', l_fs2_blocks); p ('2014 -- 50% free space bytes', l_fs2_bytes); p ('2014 -- 25% free space blocks ', rochelle fs3_blocks); p ('2017 -- 50% free space bytes ', l_fs3_bytes); p ('2017 -- 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-type table 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. 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. the segment of analyze on www.2cto.com can be like this 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.