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?
DBA_TS_QUOTAS also contains information on the assigned quota, even if the user did not create any tables.
MAX_BYTES– User’s quota in bytes, or -1 if no limit
SQL*Plus: Release 220.127.116.11.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 18.104.22.168.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>