改写自Thomas Kyte的show_space脚本

去掉了是否是ASSM表空间的判断，现在可能大家用的10G比较多了，段管理默认都是ASSM的，另外生产环境可能不允许随便建存储过程，所以写的是一个匿名PL/SQL块。

declare
l_owner varchar2(20);
l_segname varchar2(60);
l_partition varchar2(60);
l_type varchar2(20);
l_total_blocks number;
l_total_bytes number;
l_unused_blocks number;
l_unused_bytes number;
l_LastUsedExtFileId number;
l_LastUsedExtBlockId number;
l_LastUsedBlock number;
l_unformatted_blocks number;
l_unformatted_types number;
l_fs1_blocks number;
l_fs2_blocks number;
l_fs3_blocks number;
l_fs4_blocks number;
l_full_blocks number;
l_fs_bytes 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
l_segname := upper('&segname');
l_partition := upper('&partition');
l_type := upper('&type');
l_owner := upper('&owner');

if (l_owner is null) then
l_owner := user;
end if;

if (l_type is null) then
l_type := 'TABLE';
elsif (l_type = 'T') then
l_type := 'TABLE';
elsif (l_type = 'I') then
l_type := 'INDEX'; 
elsif (l_type = 'P') then
l_type := 'TABLE PARTITION';
end if;

dbms_space.space_usage
(l_owner, l_segname, l_type, l_unformatted_blocks, l_unformatted_types,
l_fs1_blocks, l_fs_bytes, l_fs2_blocks, l_fs_bytes, l_fs3_blocks, l_fs_bytes, l_fs4_blocks, l_fs_bytes, l_full_blocks, l_fs_bytes, l_partition);

p( 'Unformatted Blocks', l_unformatted_blocks);
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( 'Full Blocks', l_full_blocks);

dbms_space.unused_space
( segment_owner => l_owner,
segment_name => l_segname,
segment_type => l_type,
partition_name => l_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_LastUsedBlock );
   
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_LastUsedBlock );
end;
/

如果需要在数据库中建立一个存储过程，将匿名块前面一段代码修改为如下即可：

create or replace procedure show_space
( p_segname in varchar2,
p_partition in varchar2 default null,
p_type in varchar2 default 'TABLE',
p_owner in varchar2 default user
)
as
l_owner varchar2(20);
l_segname varchar2(60);
l_partition varchar2(60);
l_type varchar2(20);
l_total_blocks number;
l_total_bytes number;
l_unused_blocks number;
l_unused_bytes number;
l_LastUsedExtFileId number;
l_LastUsedExtBlockId number;
l_LastUsedBlock number;
l_unformatted_blocks number;
l_unformatted_types number;
l_fs1_blocks number;
l_fs2_blocks number;
l_fs3_blocks number;
l_fs4_blocks number;
l_full_blocks number;
l_fs_bytes 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
l_segname := upper(p_segname);
l_partition := upper(p_partition);
l_type := upper(p_type);
l_owner := upper(p_owner);

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

转载于:http://blog.itpub.net/339291/viewspace-682261/