计算Object(包括表，索引)的占用空间及未使用空间

计算Object(包括表，索引)的占用空间及未使用空间存储过程。

create or replace procedure get_table_blocks(v_user varchar2 default user, --object对应的user
v_objname varchar2 --对应的object name
) 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;
v_fs1_bytes number default 0;
v_fs2_bytes number default 0;
v_fs3_bytes number default 0;
v_fs4_bytes number default 0;
v_fs1_blocks number default 0;
v_fs2_blocks number default 0;
v_fs3_blocks number default 0;
v_fs4_blocks number default 0;
v_full_bytes number default 0;
v_full_blocks number default 0;
v_unformatted_bytes number default 0;
v_unformatted_blocks number default 0;
l_partname varchar2(30) default NULL;
l_segtype varchar2(30);
l_username varchar2(30); --判断当前用户名
l_recordnum number;
begin
/*参数说明：如果不输入v_user参数代表当前用户*
主要计算该表对应下的空间
*/

[@more@] if v_user is null then
select user into l_username from dual;
else
l_username :=upper(v_user);
end if;
select count(*) into l_recordnum from dba_segments
where OWNER= l_username and segment_name = upper(v_objname); --and SEGMENT_TYPE=upper(v_objtype);
if l_recordnum=1 then -- 不是分区Object类型
select SEGMENT_TYPE into l_segtype from dba_segments
where OWNER= l_username and segment_name = upper(v_objname);
dbms_space.space_usage(
segment_owner => l_username,
segment_name => upper(v_objname),
segment_type => upper(l_segtype),
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);

v_fs1_bytes :=l_fs1_bytes ;
v_fs2_bytes :=l_fs2_bytes ;
v_fs3_bytes :=l_fs3_bytes ;
v_fs4_bytes :=l_fs4_bytes ;
v_fs1_blocks :=l_fs1_blocks ;
v_fs2_blocks :=l_fs2_blocks ;
v_fs3_blocks :=l_fs3_blocks ;
v_fs4_blocks :=l_fs4_blocks ;
v_full_bytes :=l_full_bytes ;
v_full_blocks :=l_full_blocks ;
v_unformatted_bytes :=l_unformatted_bytes ;
v_unformatted_blocks :=l_unformatted_blocks;
elsif l_recordnum >1 then --分区Object 处理
for rec in (select PARTITION_NAME,SEGMENT_TYPE
from dba_segments
where OWNER= l_username and segment_name = upper(v_objname)
) loop
l_partname:=rec.PARTITION_NAME;
l_segtype :=rec.SEGMENT_TYPE;
dbms_space.space_usage(
segment_owner => l_username,
segment_name => upper(v_objname),
segment_type => upper(l_segtype),
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,
partition_name =>l_partname);

v_fs1_bytes := v_fs1_bytes + l_fs1_bytes ;
v_fs2_bytes := v_fs2_bytes + l_fs2_bytes ;
v_fs3_bytes := v_fs3_bytes + l_fs3_bytes ;
v_fs4_bytes := v_fs4_bytes + l_fs4_bytes ;
v_fs1_blocks := v_fs1_blocks + l_fs1_blocks ;
v_fs2_blocks := v_fs2_blocks + l_fs2_blocks ;
v_fs3_blocks := v_fs3_blocks + l_fs3_blocks ;
v_fs4_blocks := v_fs4_blocks + l_fs4_blocks ;
v_full_bytes := v_full_bytes + l_full_bytes ;
v_full_blocks := v_full_blocks + l_full_blocks ;
v_unformatted_bytes := v_unformatted_bytes + l_unformatted_bytes ;
v_unformatted_blocks := v_unformatted_blocks + l_unformatted_blocks;
end loop;
end if;
dbms_output.put_line(' FS1 Blocks = '||v_fs1_blocks||' Bytes ='||v_fs1_bytes);
dbms_output.put_line(' FS2 Blocks = '||v_fs2_blocks||' Bytes ='||v_fs1_bytes);
dbms_output.put_line(' FS3 Blocks = '||v_fs3_blocks||' Bytes ='||v_fs1_bytes);
dbms_output.put_line(' FS4 Blocks = '||v_fs4_blocks||' Bytes ='||v_fs1_bytes);
dbms_output.put_line('Full Blocks = '||v_full_blocks||' Bytes ='||v_full_bytes);
dbms_output.put_line('Unformatted Blocks= '||v_unformatted_blocks||' Bytes ='||v_unformatted_bytes);
end;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/42810/viewspace-983345/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/42810/viewspace-983345/