查看表和视图空间show_space()

最新推荐文章于 2021-05-10 07:46:13 发布

深圳gg

最新推荐文章于 2021-05-10 07:46:13 发布

阅读量2.2k

点赞数

分类专栏：数据库文章标签： table ext file user

本文链接：https://blog.csdn.net/stevendbaguo/article/details/7963299

版权

数据库专栏收录该内容

125 篇文章 0 订阅

订阅专栏

show_space()有很多版本，这是其中的一个。

--在PL/SQL命令窗口要
set serveroutput on
--查看表
exec show_space('watch_log_event','auto','table');
exec show_space('watch_log_event','auto','table','Y');
--查看索引
exec show_space('INDEX_TX_LOCK','auto','index');
exec show_space('INDEX_TX_LOCK','auto','index','Y');

create or replace procedure show_space 
( p_segname_1 in varchar2, 
p_space in varchar2 default 'MANUAL', 
p_type_1 in varchar2 default 'TABLE' , 
p_analyzed in varchar2 default 'N', 
p_owner_1 in varchar2 default user) 
as 
p_segname varchar2(100); 
p_type varchar2(10); 
p_owner varchar2(30); 
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; 

if (p_type_1 = 'INDEX' or p_type_1 = 'index') then  
p_type := 'INDEX'; 
end if; 
if (p_type_1 = 'TABLE' or p_type_1 = 'table') then  
p_type := 'TABLE'; 
end if; 
if (p_type_1 = 'CLUSTER' or p_type_1 = 'cluster') then  
p_type := 'CLUSTER'; 
end if; 

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


/*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 , 
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', l_fs1_bytes); 
p( '25% -- 50% free space blocks', l_fs2_blocks); 
p( '25% -- 50% free space bytes', l_fs2_bytes); 
p( '50% -- 75% free space blocks', l_fs3_blocks); 
p( '50% -- 75% free space bytes', l_fs3_bytes); 
p( '75% -- 100% free space blocks', l_fs4_blocks); 
p( '75% -- 100% free space bytes', l_fs4_bytes); 
p( 'Unused Blocks', l_unformatted_blocks ); 
p( 'Unused Bytes', l_unformatted_bytes ); 
p( 'Total Blocks', l_full_blocks); 
p( 'Total bytes', l_full_bytes); 
end if; 
end;

SQL> exec show_space('TEST','auto','table');

Total Blocks............................8
Total Bytes.............................65536
Unused Blocks...........................0
Unused Bytes............................0
Last Used Ext FileId....................6
Last Used Ext BlockId...................124225
Last Used Block.........................8

官方文档解释：

Parameter	Description
`segment_owner`	Schema name of the segment to be analyzed.
`segment_name`	Segment name of the segment to be analyzed.
`segment_type`	Type of the segment to be analyzed (`TABLE`,`INDEX`, or`CLUSTER`): `TABLE` `TABLE PARTITION` `TABLE SUBPARTITION` `INDEX` `INDEX PARTITION` `INDEX SUBPARTITION` `CLUSTER` `LOB` `LOB PARTITION` `LOB SUBPARTITION`
`total_blocks`	Returns total number of blocks in the segment.
`total_bytes`	Returns total number of blocks in the segment, in bytes.
`unused_blocks`	Returns number of blocks which are not used.
`unused_bytes`	Returns, in bytes, number of blocks which are not used.
`last_used_extent_ file_id`	Returns the file ID of the last extent which contains data.
`last_used_extent_ block_id`	Returns the starting block ID of the last extent which contains data.
`last_used_block`	Returns the last block within this extent which contains data.
`partition_name`	Partition name of the segment to be analyzed. This is only used for partitioned tables; the name of subpartition should be used when partitioning is compose.