通過 dbms_space.space_usage 檢視objects 的空間使用情況

SQL> show serveroutput
serveroutput OFF
SQL>
SQL> set serveroutput on;
SQL> show serveroutput
serveroutput ON SIZE UNLIMITED FORMAT WORD_WRAPPED
SQL> 
declare
     l_fs1_bytes number;
    l_fs2_bytes number;
    l_fs3_bytes number;
    l_fs4_bytes number;
    l_fs1_blocks number;
    l_fs2_blocks number;
    l_fs3_blocks number;
    l_fs4_blocks number;
    l_full_bytes number;
    l_full_blocks number;
    l_unformatted_bytes number;
    l_unformatted_blocks number;
  begin
      dbms_space.space_usage('DFMS',
         'IDX1_WIP_D_WO_DETAIL_COMID',
         'INDEX',
         fs1_bytes=> l_fs1_bytes,
         fs1_blocks         => l_fs1_blocks,
         fs2_bytes          => l_fs2_bytes,
         fs2_blocks         => l_fs2_blocks,
         fs3_bytes          => l_fs3_bytes,
         fs3_blocks         => l_fs3_blocks,
         fs4_bytes          => l_fs4_bytes,
         fs4_blocks         => l_fs4_blocks,
         full_bytes         => l_full_bytes,
         full_blocks        => l_full_blocks,
         unformatted_blocks => l_unformatted_blocks,
         unformatted_bytes  => l_unformatted_bytes   );
      dbms_output.put_line(' FS1 Blocks = '||l_fs1_blocks||' and Bytes = '||l_fs1_bytes);
      dbms_output.put_line(' FS2 Blocks = '||l_fs2_blocks||' and Bytes = '||l_fs2_bytes);
      dbms_output.put_line(' FS3 Blocks = '||l_fs3_blocks||' and Bytes = '||l_fs3_bytes);
      dbms_output.put_line(' FS4 Blocks = '||l_fs4_blocks||' and Bytes = '||l_fs4_bytes);
      dbms_output.put_line(' Full Blocks = '||l_full_blocks||' and Bytes = '||l_full_bytes);
      dbms_output.put_line(' Unformatted Blocks = '||l_unformatted_blocks||' and Bytes =

'||l_unformatted_bytes);
   end;
   /

FS1 Blocks = 0 and Bytes = 0
FS2 Blocks = 1548 and Bytes = 25362432
FS3 Blocks = 0 and Bytes = 0
FS4 Blocks = 0 and Bytes = 0
Full Blocks = 3405 and Bytes = 55787520
Unformatted Blocks = 3405 and Bytes = 18633981952 

PL/SQL procedure successfully completed.

SQL>

注意執行許可權 。 

------------------------------------------------- 

 

順便介紹一下  analyze  index    xxxx  validate structure ;  的使用。

SQL> analyze  index   dfms.IDX1_WIP_D_WO_DETAIL_COMID  validate structure ;

SQL> select name, del_lf_rows, lf_rows, round((del_lf_rows/(lf_rows+0.0000000001))*100)
  2   "Frag Percent"  from index_stats   where   name = 'IDX1_WIP_D_WO_DETAIL_COMID'  ;   
NAME                                                    DEL_LF_ROWS        LF_ROWS        Frag Percent
------------------------------ ----------- ---------- ------------
IDX1_WIP_D_WO_DETAIL_COMID         1732765    1817931           95   

 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/35489/viewspace-674472/，如需轉載，請註明出處，否則將追究法律責任。