How to check tablespace usage in Oracle

Check each schema usage on tablespaces.

set linesize 300
set pagesize 20
set feedback off

select sysdate, a.owner username, a.tablespace_name, round(b.total_space/1024/1024,2) "Total (MB)", round(sum(a.bytes)/1024/1024,2) "Used (MB)", round(sum(a.bytes/b.total_space)*100,2) "% Used"
from dba_segments a, (select tablespace_name, sum(bytes) total_space
                      from dba_data_files
                      group by tablespace_name) b
where a.tablespace_name not in ('SYSAUX', 'SYSTEM', 'UNDOTBS1', 'UNDOTBS2')
and a.tablespace_name = b.tablespace_name
group by a.tablespace_name, a.owner, b.total_space/1024/1024
order by a.tablespace_name, a.owner;

Check overall tablespaces usage.

set linesize 300
set pagesize 20
set feedback off

select t1.tsp_name, sysdate,
       round(t1.total_space/1024/1024,2) "Total (MB)",
       round((t1.total_space-t2.free_bytes)/1024/1024,2) "Used (MB)",
       round(t2.free_bytes/1024/1024,2) "Free (MB)",
       round(t2.free_bytes/t1.total_space*100,2) "% Free",
       decode(greatest(round(t2.free_bytes/t1.total_space*100,2),20),20,'*',' ') D,
       decode(greatest(round(t2.free_bytes/t1.total_space*100,2),15),15,'*',' ') E
from (select tablespace_name tsp_name, sum(bytes) total_space
      from sys.dba_data_files
      where tablespace_name not in ('UNDOTBS1', 'UNDOTBS2')
      group by tablespace_name) t1,
     (select tablespace_name tsp_name, sum(bytes) free_bytes
      from sys.dba_free_space
      where tablespace_name not in ('UNDOTBS1', 'UNDOTBS2')
      group by tablespace_name) t2
where t1.tsp_name = t2.tsp_name
order by tsp_name;