Oracle tablespace space usage stats.

In Oracle you can look tablespace space usage stats from couple of places.


You can use dba_data_files and dba_segments to check free space, used space, total space, plus percents of free space. Here is example:
SQL> select dfiles.tablespace_name "Tablespace", totalusedspace "Used MB",
 (dfiles.totalspace - tusage.totalusedspace) "Free MB",
 dfiles.totalspace "Total MB",
 round(100 * ( (dfiles.totalspace - tusage.totalusedspace)/ dfiles.totalspace)) "Free %"
 from
 (select tablespace_name,
 round(sum(bytes) / 1048576) TotalSpace
 from dba_data_files
 group by tablespace_name) dfiles,
 (select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_name
 from dba_segments
 group by tablespace_name) tusage
 where dfiles.tablespace_name = tusage.tablespace_name ;

Tablespace                        Used MB    Free MB   Total MB     Free %
------------------------------ ---------- ---------- ---------- ----------
TEST2_DATA                               3        253         256           99
SYSAUX                                 1094        56          1150           5
UNDOTBS1                              145       2070       2215          93
TEST_DATA                            1369       443        1812           24


Or you can use dba_free_space to check free (usable) space in tablespace. Here is example:
SQL> select tablespace_name , sum(bytes)/1024/1024 "free space Mb" from dba_free_space group by tablespace_name;

TABLESPACE_NAME         free space Mb
------------------------------ -------------
TEST2_DATA                        251.625
SYSAUX                               55.25
UNDOTBS1                          1928.0625
TEST_DATA                          573.25



NOTE!! (in Oracle 10/11) If you see differences between these two selects  (free space might be different) that is because dba_free_space does not show data that is in recyclebin but dba_segment shows that data. If you delete data from table or whole table/index that data goes into recycle bin (it is still in the same tablespace) but space used by recyclebin can be used for new data if it is needed. And this is why dba_free_space does not show it as used space. You can clean recyclebin with purge.

For certain user recyclebin:
SQL> purge recyclebin;

All recyclebins (with user that have privileges):
SQL> purge dba_recyclebin;