ORACLE 查询高水位表 脚本

select a.owner,
       a.num_rows,
       round(a.avg_row_len * a.num_rows / 1024 / 1024 / 0.9, 10) as need,
       round(a.blocks * 8 / 1024, 10) true,
       round((blocks * 8 / 1024 -
             avg_row_len * num_rows / 1024 / 1024 / 0.9),
             10) as recover_mb,
       a.table_name as hvm_table_name
  from dba_tables a
 where a.owner not in ('SYS', 'SYSMAN', 'SYSTEM')
   and a.blocks * 8 / 1024 - avg_row_len * num_rows / 1024 / 1024 / 0.9 > 100
 order by recover_mb desc;

 

 

手工干的话，使用dbms_space包，特别是unused_space过程。

 

 

我更愿意使用auto space advisor来做：