Show_HWM.sql

create or replace procedure show_space(p_segment in varchar2,

p_owner in varchar2 default user,

p_type in varchar2 default 'TABLE',

p_partition in varchar2 default NULL) as

l_total_blocks number;

l_total_bytes number;

l_unused_blocks number;

l_unused_bytes number;

l_LastUsedFileId number;

l_LastUsedBlockId 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

dbms_space.unused_space(segment_owner => p_owner,

segment_name => p_segment,

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_LastUsedFileId,

last_used_extent_block_id => l_LastUsedBlockId,

last_used_block => l_last_used_block,

partition_name => p_partition);

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 extend file id', l_LastUsedFileId);

p('Last Used extend Block id', l_LastUsedBlockId);

p('Last Used Block', l_last_used_block);

end;

/