Thanks for the question, Abdul Wahid.
Asked: September 20, 2017 - 2:58 pm UTC
Last updated: September 21, 2017 - 12:49 am UTC
Version: 10.2
Viewed 10K+ times! This question is 
You Asked
When I tried to check the TBS quota for each user in the database using the below view it gave info about only the SYSMAN user
SQL> select TABLESPACE_NAME,USERNAME,MAX_BYTES,MAX_BLOCKS from DBA_TS_QUOTAS;
TABLESPACE_NAME USERNAME MAX_BYTES MAX_BLOCKS
------------------------------ ------------------------------ ---------- ----------
SYSAUX SYSMAN -1 -1
Then when I checked the DBA_SYS_PRIVS view it gave info about all the users in the DB having unlimited TBS quota
SQL> select grantee, PRIVILEGE from DBA_SYS_PRIVS where PRIVILEGE = 'UNLIMITED TABLESPACE' order by 1;
GRANTEE PRIVILEGE
------------------------------ ----------------------------------------
ADT UNLIMITED TABLESPACE
AHC UNLIMITED TABLESPACE
AMB UNLIMITED TABLESPACE
BB UNLIMITED TABLESPACE
BILLING UNLIMITED TABLESPACE
.
.
.
Could you explain why this info was not available in the DBA_TS_QUOTAS view ?
and Connor said...
There two different things in play here.
I can give you the *privilege* of UNLIMITED TABLESPACE, which means you use as much space in *any* tablespace. That is what you find in DBA_SYS_PRIVS
I can also give an unlimited amount of space in a *particular* tablespace, using:
alter user MY_USER quota unlimited on MY_TSPACE;
That is what you would see in DBA_TS_QUOTAS
Hope this helps clarify things.
Is this answer out of date? If it is, please let us know via a Comment