ORACLE存储过程show_space完整版

show_space的版本在网络上有不少版本. 今天整理出一个通用的版本, 以后用.

create or replace procedure show_space(v_segment_name in varchar2, v_segment_owner 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); 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_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_owner, 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_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, 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, freelist_group_id => 0, free_blks => l_free_blks); p('Free Blocks', l_free_blks); end if; end if; end;