6

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?

0

1 Answer 1

9

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>

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.