Oracle Database check tablespace usage and size.

Many production oracle database instances do not have auto incrementing tablespaces. You would need to know beforehand before users start getting ORA-01688 unable to extend tablespace errors.

You would probably have some sort of monitoring on the tablespaces already , OEM shows alerts about tablespaces readily as well. But its good to have a query handy to quickly check the tablespace usages you have in your system.

select df.tablespace_name ,totalusedspace  "totalusedspace_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 ;