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
tablespace DATABASE SCRIPTS