Oracle HowTo:如何查看各个表空间的自由空间

DBA_FREE_SPACE视图记录了数据库中所有表空间的自由extents情况，所以可以从该视图获得各表空间自由空间情况。

SQL> desc dba_free_space
 Name                         Null?    Type
 ----------------------- ------------- -----------------
 TABLESPACE_NAME                       VARCHAR2(30)
 FILE_ID                               NUMBER
 BLOCK_ID                              NUMBER
 BYTES                                 NUMBER
 BLOCKS                                NUMBER
 RELATIVE_FNO                          NUMBER

可以使用如下SQL进行查询:

select tablespace_name,sum(bytes)/1024/1024 free_Mbytes
from dba_free_space
group by tablespace_name
order by free_Mbytes
/

示例输出:

SQL> select tablespace_name,sum(bytes)/1024/1024 free_Mbytes
  2  from dba_free_space
  3  group by tablespace_name
  4  order by free_Mbytes
  5  /

TABLESPACE_NAME      FREE_MBYTES
-------------------- -----------
USERS                        .75
UNDOTBS1                 18.6875
SYSTEM                   42.6875