query to check tablespace size and freespace

[oracle@oeldev ~]$ export ORACLE_SID=HRDEMO
[oracle@oeldev ~]$ sqlplus / as sysdba;

SQL*Plus: Release 11.2.0.3.0 Production on Mon Jul 17 13:02:33 2017

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>
col "Tablespace" for a22
col "Used MB" for 99,999,999col "Free MB" for 99,999,999
col "Total MB" for 99,999,999
SQL>
SQL> select df.tablespace_name "Tablespace",
    totalusedspace "Used MB",
    (df.totalspace - tu.totalusedspace) "Free MB",
    df.totalspace "Total MB",
    round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace))
    "Pct. Free"
    from
    (select tablespace_name,
    round(sum(bytes) / 1048576) TotalSpace
   from dba_data_files
   group by tablespace_name) df,
   (select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_name
   from dba_segments
   group by tablespace_name) tu
   where df.tablespace_name = tu.tablespace_name ;

Tablespace                 Used MB     Free MB    Total MB  Pct. Free
---------------------- ----------- ----------- ----------- ----------
BNAPP                           14          38          52         73
HRLARGE                      39          52          91         57
HRAPP2                          6          12          18          67
EOAPP                            1          24          25          96
PTTLRG                          336     186         522        36
COAPP                            1           6           7            86
HTAPP                            0           3           3            100
PSIMAGE2                     1,106    64        1,170        5
PSUNDOTS                    33       2,187     2,220       99
SYSAUX                          615       45         660         7
PTTBL                            132       132         264       50

Tablespace                 Used MB     Free MB    Total MB  Pct. Free
---------------------- ----------- ----------- ----------- ----------
PSIMAGE                        911         902       1,813         50
PTAMSG                           4          39          43         91
PTWORK                           1          11          12         92
PTPRJWK                          0           8           8        100
PSIMGR                            2           2           4         50
CULARGE                          0           6           6        100
PAAPP                              12          47          59         80
EPAPP                               2          13          15         87
STWORK                           0          17          17        100
PIAPP                               2           7           9         78
PILARGE                          1           3           4         75

Tablespace                 Used MB     Free MB    Total MB  Pct. Free
---------------------- ----------- ----------- ----------- ----------
PTPRC                            3           9          12         75
STLARGE                       4           9          13         69
HRAPP3                          2           5           7         71
SAAPP                          100         200         300         67
HRAPP6                          2          33          35         94
TLAPP                           19          81         100         81
BNLARGE                     15          12          27         44
HRSWORK                      0          15          15        100
SYSTEM                       706       1,294       2,000         65
AAAPP                            7          15          22         68
SRAPP                           65          86         151         57

Tablespace                 Used MB     Free MB    Total MB  Pct. Free
---------------------- ----------- ----------- ----------- ----------
HPAPP                           37          38          75         51
GIAPP                            2           6           8         75
FALARGE                      9           8          17         47
HRSAPP                       16          48          64         75
PVAPP                            0           4           4        100
PTLOCK                         0           3           3        100
PSDEFAULT                  0         100         100        100
HRAPP                          97         253         350         72
PSINDEX                   2,966       3,034       6,000         51
ADAPP                           11          49          60         82
CCAPP                           12         238         250         95

Tablespace                 Used MB     Free MB    Total MB  Pct. Free
---------------------- ----------- ----------- ----------- ----------
SALARGE                        1           8           9         89
PTAPPE                           2           3           5         60
PTAPP                           16          34          50         68
TLLARGE                        5          35          40         88
GPAPP                          349         811       1,160         70
EOEWAPP                       0           9           9        100
ERAPP                            1          12          13         92
HRSLARGE                    1           7           8         88
HRWORK                        0          20          20        100
PYAPP                           37          73         110         66
FAAPP                          173         127         300         42

Tablespace                 Used MB     Free MB    Total MB  Pct. Free
---------------------- ----------- ----------- ----------- ----------
HRAPP1                           6          16          22         73
HRAPP7                           1           7           8         88
AVAPP                            6          13          19         68
HRAPP5                           0          13          13        100
PTRPTS                           1           2           3         67
EOECAPP                          4          21          25         84
EPLARGE                          2           8          10         80
FGAPP                           11          24          35         69
PALARGE                          2           5           7         71
PCLARGE                          0           5           5        100
AALARGE                          1           6           7         86

Tablespace                 Used MB     Free MB    Total MB  Pct. Free
---------------------- ----------- ----------- ----------- ----------
STAPP                            9          23          32         72
HRAPP4                           4          12          16         75
PY0LRG                          76          53         129         41
FSAPP                            2           3           5         60
PCAPP                            1           4           5         80
EOCFAPP                          3          17          20         85
EOIUAPP                          0           3           3        100
PTTREE                           3           3           6         50

74 rows selected.

SQL>