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 an user in oracle.

Using below query you can get the tablespace 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 above query:

Get Tablespace Quota Details

HOW TO GET TABLESPACE QUOTA DETAILS FOR A PARTICULAR USER

Using below query you can find out the tablespace quota details for a particular user in 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 above query:

GET TABLESPACE QUOTA

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

Read: How To Get Ddl Of All Tablespaces In Oracle

subscribe on youtube

1 thought on “How To Get Tablespace Quota Details Of An User In Oracle”

Leave a Comment

%d bloggers like this: