How To Get Tablespace Quota Details Of An User In Oracle

In this article, we are going to show you how to get tablespace quota details of a user in oracle.

What is tablespace quota in Oracle?

In the Oracle database when we set the quota for a user, that means we set the limit of data storage for particular schemas objects in the specified tablespace.

Using the below query you can get the table space quota details of the 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
order by
1,2
/

Output from the above query:

HOW TO GET TABLE SPACE QUOTA DETAILS FOR A PARTICULAR USER

Using the below query you can find out the table space quota details for a particular user in a live environment.

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 from the above query:

I hope you find this article useful, please write in the comment box something about this article.

Read: How To Get Ddl Of All Tablespaces In Oracle