Monitoring your Oracle DB Tablespace

To get Database utilization

select    round(sum(used.bytes) / 1024 / 1024 / 1024 ) || ' GB' "Database Size"
,    round(sum(used.bytes) / 1024 / 1024 / 1024 ) -
    round(free.p / 1024 / 1024 / 1024) || ' GB' "Used space"
,    round(free.p / 1024 / 1024 / 1024) || ' GB' "Free space"
, round(((sum(used.bytes) - free.p) / sum(used.bytes)) * 100) || '% ' "PERC UTIL"
from    (select    bytes
    from    v$datafile
    union    all
    select    bytes
    from     v$tempfile
    union     all
    select     bytes
    from     v$log) used
,    (select sum(bytes) as p
    from dba_free_space) free
group by free.p;

--- To check TABLE SPACE UTILIZATION

-- Assumption: using "TBS_DATA" table space

select a.tablespace_name                                              name,
       b.tablespace_name                                              dummy,
       sum(b.bytes)/count( distinct a.file_id||'.'||a.block_id ) / 1024 / 1024 / 1024 || ' GB' "Database Size" ,
       ((sum(b.bytes)/count( distinct a.file_id||'.'||a.block_id ) -
       sum(a.bytes)/count( distinct b.file_id )) / 1024/ 1024/ 1024) || ' GB' "Used Table Space",
       round(sum(a.bytes)/count( distinct b.file_id ) / 1024/ 1024/ 1024) || ' GB' "Free Table Space",
       round( 100 * ( (sum(b.bytes)/count( distinct a.file_id||'.'||a.block_id )) -
               (sum(a.bytes)/count( distinct b.file_id ) )) /
       (sum(b.bytes)/count( distinct a.file_id||'.'||a.block_id ))) || '%' "PERCENT UTIL"
from sys.dba_free_space a, sys.dba_data_files b
where a.tablespace_name = 'TBS_DATA'
and a.tablespace_name = b.tablespace_name
group by a.tablespace_name, b.tablespace_name;


(thank you Sambhav for the scripts)

Another cool script :

select t.tablespace_name, f.file_name, round(bytes/1024/1024/1024) GB, AUTOEXTENSIBLE, round(maxbytes/1024/1024/1024) MAX_GB

from dba_tablespaces t, dba_data_files f

where t.tablespace_name=f.tablespace_name

order by 1,2;