Find the space allocated or used by specific user in the tablespaces

col username for a10
col used format 999,999,990
select   username,  tablespace_name, 
  decode(greatest(max_bytes, -1), 
    -1, 'Unrestricted', 
    to_char(max_bytes/1024/1024, '999,999,990') 
  )      quota_MB, 
  bytes/1024/1024   used_MB
from 
  dba_ts_quotas  where max_bytes!=0 or   bytes!=0 
order by 1,2; 



USERNAME   TABLESPACE_NAME                QUOTA_MB        USED_MB
---------- ------------------------------ ------------ ----------
AUDSYS     SYSAUX                         Unrestricted      37.75
LBACSYS    SYSTEM                         Unrestricted      .3125
MDSYS      SYSAUX                         Unrestricted      244.5
MDSYS      SYSTEM                         Unrestricted          0
OUTLN      SYSTEM                         Unrestricted      .5625