How can I check if a user have a quota on tablespace

Question

My objective is to check users quotas on tablespace:

Unfortunately the table dba_ts_quotas gives the details of already used space on tablespaces.

I want to see all users who have quotas on tbs even they don't use any space on the tablespace.

is there solution to check if a user has quota on a tbs?

Answer 1

DBA_TS_QUOTAS also contains information on the assigned quota, even if the user did not create any tables.

From the manual

MAX_BYTES - User's quota in bytes, or -1 if no limit

Example:

SQL*Plus: Release 12.1.0.2.0 Production on Tue Oct 18 10:35:29 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> select tablespace_name, username, bytes, max_bytes
  2  from dba_ts_quotas
  3  where tablespace_name = 'USERS'
  4    and username = 'FOOBAR';

no rows selected

SQL> create user foobar
  2    identified by welcome
  3    default tablespace users
  4    quota 10M on users;

User created.

SQL> select tablespace_name, username, bytes, max_bytes
  2  from dba_ts_quotas
  3  where tablespace_name = 'USERS'
  4    and username = 'FOOBAR';

TABLE USERNA      BYTES  MAX_BYTES
----- ------ ---------- ----------
USERS FOOBAR          0   10485760

1 row selected.

SQL>