show_space(查看表空间使用情况)

最新推荐文章于 2024-09-06 09:56:08 发布

翻译最新推荐文章于 2024-09-06 09:56:08 发布 · 2.4k 阅读

被引用专栏收录该内容

40 篇文章

订阅专栏

本文提供两个Oracle存储过程，分别用于检查表空间中已使用和未使用的块数量及字节数，并统计ASSM中不同自由空间比例的块数。通过这些过程可以有效监控数据库的空间使用情况。

第一个过程，这个过程是用来查看表空间中已经使用的块（字节），未使用的块（字节）

create or replace procedure show_space(

p_segname 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_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
dbms_space.unused_space (
segment_owner =>p_owner,
segment_name =>p_segname,
segment_type =>p_type,
partition_name =>p_partition,
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);

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

第二个过程，这个过程是用来查看assm中各种块的使用量的统计：

create or replace procedure show_space_assm(
p_segname in varchar2,
p_owner in varchar2 default user,
p_type in varchar2 default 'TABLE')
as
l_fs1_bytes number;
l_fs2_bytes number;
l_fs3_bytes number;
l_fs4_bytes number;

l_fs1_blocks number;
l_fs2_blocks number;
l_fs3_blocks number;
l_fs4_blocks number;

l_full_bytes number;
l_full_blocks number;

l_unformatted_bytes number;
l_unformatted_blocks 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.space_usage (
segment_owner =>p_owner,
segment_name =>p_segname,
segment_type =>p_type,
fs1_bytes =>l_fs1_bytes,
fs1_blocks =>l_fs1_blocks,

fs2_bytes =>l_fs2_bytes,
fs2_blocks =>l_fs2_blocks,

fs3_bytes =>l_fs3_bytes,
fs3_blocks =>l_fs3_blocks,

fs4_bytes =>l_fs4_bytes,
fs4_blocks =>l_fs4_blocks,

full_bytes =>l_full_bytes,
full_blocks =>l_full_blocks,

unformatted_blocks =>l_unformatted_blocks,
unformatted_bytes =>l_unformatted_bytes);

p('free space 0-25% blocks:' , l_fs1_blocks);
p('free space 25-50% blocks:' , l_fs2_blocks);
p('free space 50-75% blocks:' , l_fs3_blocks);
p('free space 75-100% blocks:' , l_fs4_blocks);

p('Full Blocks:' , l_full_blocks);
p('Unformatted Blocks:' , l_unformatted_blocks);

end;
/