查询数据库对象block使用情况

1.定义获取db_block_info Function

create or replace function get_block_info
   ( p_segname in varchar2,
     p_owner   in varchar2 default user,
     p_type    in varchar2 default 'TABLE',
     p_partition in varchar2 default NULL )
   return number
   authid current_user
   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;
   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 );
 
       return l_total_blocks || '.' || (l_total_blocks-l_unused_blocks);
   exception
       when others then return NULL;
   end;

2.查询对应schema下数据库对象block使用情况

select table_name, trunc(gbi) total_blocks, to_number(substr(gbi,instr(gbi,'.')+1)) used_blocks
      from ( select table_name, get_block_info( table_name ) gbi, rownum
               from user_tables where username='AIATOUCH'
               )

原文整理来自ASKTom,还有大家常用的 show_space.