view tablespace list with used, free, total space and free space percentage

col "Tablespace" for a22
col "Used MB" for 99,999,999
col "Free MB" for 99,999,999
col "Total MB" for 99,999,999

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
---------------------- ----------- ----------- ----------- ----------
AAAPP                       57,847         515      58,362          1
ADAPP                            1          59          60         98
AVAPP                            0          19          19        100
BNAPP                           21          31          52         60
BNLARGE                          1          26          27         96
CCAPP                            3         247         250         99
COAPP                           12           5          17         29
CULARGE                          0           6           6        100
EOAPP                           69           6          75          8
EOCFAPP                          3          17          20         85
EOECAPP                          5          20          25         80

Tablespace                 Used MB     Free MB    Total MB  Pct. Free
---------------------- ----------- ----------- ----------- ----------
EOECLRG                        536          31         567          5
EOEWAPP                          0           9           9        100
EOIUAPP                          0           3           3        100
EPAPP                           30           5          35         14
EPLARGE                        498          27         525          5
ERAPP                            4           9          13         69
FAAPP                           29         271         300         90
FGAPP                            8          27          35         77
FSAPP                            0           5           5        100
GIAPP                            0           8           8        100
GPAPP                       38,207       2,705      40,912          7

Tablespace                 Used MB     Free MB    Total MB  Pct. Free
---------------------- ----------- ----------- ----------- ----------
HPAPP                        2,444         126       2,570          5
HRAPP                       26,326       1,319      27,645          5
HRAPP1                           2          20          22         91
HRAPP2                           6          12          18         67
HRAPP3                           0           7           7        100
HRAPP4                           3          13          16         81
HRAPP5                           2          11          13         85
HRAPP6                           1          34          35         97
HRAPP7                          25          23          48         48
HRLARGE                        369          22         391          6
HRSAPP                          23          41          64         64

Tablespace                 Used MB     Free MB    Total MB  Pct. Free
---------------------- ----------- ----------- ----------- ----------
HRSLARGE                         6           2           8         25
HRSWORK                          0          15          15        100
HRWORK                           0          20          20        100
PAAPP                            3          56          59         95
PIAPP                            0           9           9        100
PSDEFAULT                    1,900         148       2,048          7
PSIMAGE                      4,365         223       4,588          5
PSIMAGE2                       928          52         980          5
PSIMGR                           2           2           4         50
PSINDEX                     40,952       1,993      42,945          5
PSUNDOTS1                   27,986       4,777      32,763         15

Tablespace                 Used MB     Free MB    Total MB  Pct. Free
---------------------- ----------- ----------- ----------- ----------
PSUNDOTS2                   12,407      20,356      32,763         62
PTAMSG                         344       3,619       3,963         91
PTAPP                           15          35          50         70
PTAPPE                           2           3           5         60
PTAUDIT                         15           2          17         12
PTLOCK                           0           3           3        100
PTPRC                           29           3          32          9
PTPRJWK                          0           8           8        100
PTRPTS                         112          11         123          9
PTTBL                        2,982         152       3,134          5
PTTLRG                         454          68         522         13

Tablespace                 Used MB     Free MB    Total MB  Pct. Free
---------------------- ----------- ----------- ----------- ----------
PTTREE                           9           2          11         18
PTWORK                           1          11          12         92
PVAPP                            0           4           4        100
PY0LRG                           0         129         129        100
PYAPP                           12          98         110         89
SAAPP                            7         293         300         98
SRAPP                            1         150         151         99
STAPP                            1          31          32         97
STWORK                           0          17          17        100
SYSAUX                       1,572       2,158       3,730         58
SYSTEM                      14,346      33,782      48,128         70

Tablespace                 Used MB     Free MB    Total MB  Pct. Free
---------------------- ----------- ----------- ----------- ----------
TLAPP                            7          93         100         93
TLLARGE                          0          40          40        100
USERS                            0           5           5        100

69 rows selected.



View details of specific tablespace:
=======================
set linesize 124
col file_name format a75
col AUTOEXTENSIBLE format a18
SELECT FILE_NAME, BYTES/(1024*1024), MAXBYTES/(1024*1024), AUTOEXTENSIBLE from DBA_DATA_FILES
WHERE TABLESPACE_NAME='AAAPP';


FILE_NAME                                                                   BYTES/(1024*1024) MAXBYTES/(1024*1024)
--------------------------------------------------------------------------- ----------------- --------------------
AUTOEXTENSIBLE
------------------
+ORA_DB/hrprd/datafile/aaapp.625.852581469                                              32762           32767.9844
YES

+ORA_DB/hrprd/datafile/aaapp.741.947934975                                              10240                 7168
YES

+ORA_DB/hrprd/datafile/aaapp.851.948719703                                               5120                 5120
YES