查询表空间使用率

select total.tablespace_name,
    to_char(round((1 - free.MB / total.MB) * 100, 2),'990.99') || '%' as Used_Pct
from   
    (select tablespace_name, sum(bytes) / 1024 / 1024 as MB
    from dba_free_space
    group by tablespace_name) free,
   
    (select tablespace_name, sum(bytes) / 1024 / 1024 as MB
    from dba_data_files
    group by tablespace_name) total
where
    free.tablespace_name = total.tablespace_name ;
   

第二版会展示占用率和当前空间大小和已经使用大小。

select total.tablespace_name,
    to_char(round((1 - free.MB / total.MB) * 100, 2),'990.99') || '%' as Used_Pct,
    free.MB as freeMB,
    total.MB as totalMB
from   
    (select tablespace_name, sum(bytes) / 1024 / 1024 as MB
    from dba_free_space
    group by tablespace_name) free,
   
    (select tablespace_name, sum(bytes) / 1024 / 1024 as MB
    from dba_data_files
    group by tablespace_name) total
where
    free.tablespace_name = total.tablespace_name ;

样例输出(version 2, version 1 没有后面两列)：

TABLESPACE_NAME                USED_PCT FREEMB                 TOTALMB               
------------------------------ -------- ---------------------- ----------------------
SYSAUX                           94.44% 58                     1042.375              
INFOX_WEB_MAIN_DATA              91.67% 62.5                   750                   
INFOX_MAIN_DATA                   0.35% 498.25                 500                   
INFOX_RESEND                      2.66% 194.6875               200                   
INFOX_UNIWEB_MAIN_DATA            1.82% 77.5625                79                    
USERS                            77.74% 65.125                 292.5625              
INFOX_WEB_MAIN_LOG                1.84% 775.4375               790                   
INFOX_UNIWEB_MAIN_INDEX           0.16% 38.9375                39                    
SYSTEM                           99.33% 5.6875                 850                   
INFOX_CONGESTION                 10.38% 89.625                 100                   
UNDOTBS2                         63.32% 375.5625               1024                  
INFOX_MAIN_INDEX                  0.05% 799.5625               800                   
INFOX_TRAFFIC                     8.50% 91.5                   100                   
INFOX_BACKUP_INDEX               28.98% 355.125                500                   
INFOX_UNIWEB_MAIN_LOG             0.40% 78.6875                79