ECSCDAP1P > select tablespace_name, 'Tablespace needs more space.'
  2  from dba_tablespaces a
  3  where tablespace_name not like '%TEMP%'
  4  and not exists
  5  (select 1 from dba_data_files b   -- if five gig of extendable space, tablespace OK
  6  where b.tablespace_name=a.tablespace_name
  7  and autoextensible='YES'
  8  and maxbytes/1024/1024-bytes/1024/1024 >=4999
  9  and maxbytes > 0)
 10  and not exists
 11  (select 1 from dba_free_space c  -- if five gig free within any datafile, tablespace OK
 12  where c.tablespace_name=a.tablespace_name
 13  and bytes/1024/1024 >= 4999);

TABLESPACE_NAME                'TABLESPACENEEDSMORESPACE.'
------------------------------ ----------------------------
SYSAUX                         Tablespace needs more space.
SYSTEM                         Tablespace needs more space.