測試dbms_space包顯示不同物件的空間使用情況

###測試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.