###测试dbms_space.space_usage查看不同segment的空间使用情况,详细用法请查看官方手册

SQL> r
  1  declare
  2  unf number;
  3  unfb number;
  4  fs1 number;
  5  fs1b number;
  6  fs2 number;
  7  fs2b number;
  8  fs3 number;
  9  fs3b number;
 10  fs4 number;
 11  fs4b number;
 12  full number;
 13  fullb number;
 14  begin
 15  dbms_space.space_usage('SCOTT','T_ONLY','TABLE',unf,unfb,fs1,fs1b,fs2,fs2b,fs3,fs3b,fs4,fs4b,full,fullb);
 16  dbms_output.put_line('unformatted_blocks '||unf);
 17  dbms_output.put_line('unformatted_bytes '||unfb);
 18  dbms_output.put_line('Number of blocks that has at least 0 to 25% free space '||fs1);
 19  dbms_output.put_line('Number of bytes that has at least 0 to 25% free space '||fs1b);
 20  dbms_output.put_line(fs2||' '||fs3||' '||fs4||' '||full);
 21* end;
unformatted_blocks 0
unformatted_bytes 0
Number of blocks that has at least 0 to 25% free space 0
Number of bytes that has at least 0 to 25% free space 0
0 0 0 11889

##这里的fs1是指具备0-25%以内的空闲空间未使用的块个数,其它fs同理,full是具备块完全使用的块个数

###下列输入列为dbms_space.unused_space包
total_blocks
 Returns total number of blocks in the segment.  ##segment的块数
 
total_bytes
 Returns total number of blocks in the segment, in bytes.##segment的bytes数
 
unused_blocks
 Returns number of blocks which are not used. ##未使用的block数
 
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.##包括数据的最后一个extent所对应的file_id
 
last_used_extent_ block_id
 Returns the starting block ID of the last extent which contains data.###包括数据的最后一个extent的开始block id
 
last_used_block
 Returns the last block within this extent which contains data.###包括数据的extent的最后一个block
 
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.