查询Oracle表空间使用情况非常重要，oracle也提供了dba_free_space、dba_data_files等表供查询，下面提供查询表空间的剩余、表空间的总容量、表空间使用率之法。
1.查询表空间的剩余
SQL> select
tablespace_name,
count(*) as extends,
round(sum(bytes)/1024/1024, 2) as MB,
sum(blocks) as blocks
from dba_free_space group by tablespace_name;
TABLESPACE_NAME EXTENDS MB BLOCKS
------------------------------ ---------- ---------- ----------
UNDOTBS1 72 8610.19 1102104
SYSAUX 220 1044.25 133664
USERS 1 4.56 584
SYSTEM 2 4.19 536
RADSPACE 451 1389303.81 177830888
2. 查询表空间的总容量
SQL> select tablespace_name, sum(bytes)/1024/1024 as MB from dba_data_files group by tablespace_name;
TABLESPACE_NAME MB
------------------------------ ----------
SYSAUX 1510
UNDOTBS1 9215
USERS 5
SYSTEM 510
RADSPACE 1908767.98
3. 查询表空间使用率
SQL> select
total.tablespace_name,
round(total.MB,2) as Total_MB,
round(total.MB-free.MB, 2) as Used_MB,
round((1-free.MB/total.MB)*100, 2) 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;
TABLESPACE_NAME TOTAL_MB USED_MB USED_PCT
------------------------------ ---------- ---------- ----------
UNDOTBS1 9215 602.81 6.54
SYSAUX 1510 473.31 31.35
USERS 5 .44 8.75
SYSTEM 510 505.81 99.18
RADSPACE 1908767.98 520296.17 27.26
