个人简介
偶尔有空上来看看
文章分类
文章存档
最近访客
相关博文
- ·
- ·
- ·
- ·
- ·
- ·
- ·
- ·
- ·
- ·
分类： oracle
2021-05-07 17:28:24
仅供参考
-
create or replace procedure show_space(p_segname_1 in varchar2,
-
p_type_1 in varchar2 default 'table',
-
p_space in varchar2 default 'auto',
-
p_analyzed in varchar2 default 'n',
-
p_partition_1 in varchar2 default null,
-
p_owner_1 in varchar2 default user)
-
authid current_user as
-
/*
- -- grant execute on sys.dbms_space to scott; -- 普通用户
- -- exec dbms_stats.gather_table_stats('scott','emp') -- 提前收集统计信息
-
-- exec show_space('emp', 'table', 'auto', 'y', null, 'scott')
-
*/
-
p_segname varchar2(100);
-
p_type varchar2(30);
-
p_owner varchar2(30);
-
p_partition varchar2(50);
-
-
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);
-
p_owner := upper(p_owner_1);
-
p_type := p_type_1;
-
p_partition := upper(p_partition_1);
-
-
if (p_type_1 = 'i' or p_type_1 = 'i') then
-
p_type := 'index';
-
end if;
-
-
if (p_type_1 = 't' or p_type_1 = 't') then
-
p_type := 'table';
-
end if;
-
-
if (p_type_1 = 'tp' or p_type_1 = 'tp') then
-
p_type := 'table partition';
-
end if;
-
-
if (p_type_1 = 'ip' or p_type_1 = 'ip') then
-
p_type := 'index partition';
-
end if;
-
-
if (p_type_1 = 'c' or p_type_1 = 'c') then
-
p_type := 'cluster';
-
end if;
-
-
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);
-
-
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,
-
partition_name => p_partition,
-
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(mb)', round(l_total_bytes/1024/1024,1));
-
p('unused blocks', l_unused_blocks);
-
p('unused bytes(mb)', round(l_unused_bytes/1024/1024,1));
-
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,
-
partition_name => p_partition,
-
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(mb)', round(l_fs1_bytes/1024/1024,1));
-
p('25% -- 50% free space blocks', l_fs2_blocks);
-
p('25% -- 50% free space bytes(mb)', round(l_fs2_bytes/1024/1024,1));
-
p('50% -- 75% free space blocks', l_fs3_blocks);
-
p('50% -- 75% free space bytes(mb)', round(l_fs3_bytes/1024/1024,1));
-
p('75% -- 100% free space blocks', l_fs4_blocks);
-
p('75% -- 100% free space bytes(mb)', round(l_fs4_bytes/1024/1024,1));
-
p('unused blocks', l_unformatted_blocks);
-
p('unused bytes(mb)', round(l_unformatted_bytes/1024/1024,1));
-
p('total blocks', l_full_blocks);
-
p('total bytes(mb)', round(l_full_bytes/1024/1024,1));
-
end if;
-
end;
- /
效果图
效果图2
给主人留下些什么吧！~~