This script reports the tablespace quota allocated for each database user and the amount of tablespace consumed by that user.

TABLESPACE QUOTA DETAILS OF ALL THE USERS:

set pagesize 200
set lines 200
col ownr format a20         justify c heading 'Owner' 
col name format a20         justify c heading 'Tablespace' trunc 
col qota format a12         justify c heading 'Quota (KB)' 
col used format 999,999,990 justify c heading 'Used (KB)' 
set colsep '|'
select 
  username          ownr, 
  tablespace_name   name, 
  decode(greatest(max_bytes, -1), 
    -1, 'UNLIMITED', 
    to_char(max_bytes/1024, '999,999,990') 
  )                 qota, 
  bytes/1024        used 
from 
  dba_ts_quotas 
where 
  max_bytes!=0 
    or 
  bytes!=0 
order by 
  1,2 
/ 

TABLESPAE QUOTA DETAILS FOR A PARTICULAR USER:

set pagesize 200
set lines 200
col ownr format a20         justify c heading 'Owner' 
col name format a20         justify c heading 'Tablespace' trunc 
col qota format a12         justify c heading 'Quota (KB)' 
col used format 999,999,990 justify c heading 'Used (KB)' 
set colsep '|'
select 
  username          ownr, 
  tablespace_name   name, 
  decode(greatest(max_bytes, -1), 
    -1, 'UNLIMITED', 
    to_char(max_bytes/1024, '999,999,990') 
  )                 qota, 
  bytes/1024        used 
from 
  dba_ts_quotas 
where 
 ( max_bytes!=0 
    or 
  bytes!=0) and username='&USERNAME'
order by 
  1,2 
/ 

OUTPUT:

Enter value for username: AKHEHIFA
old  14:   bytes!=0) and username='&USERNAME'
new  14:   bytes!=0) and username='AKHEHIFA'

       Owner        |     Tablespace     | Quota (KB) | Used (KB)
--------------------|--------------------|------------|------------
AKHEHIFA            |USERS               |   1,048,576|           0