How to Check User Quota on Oracle Tablespace - DBA_TS_QUOTAS

advertisements

_____________________________________________________________________________________________________________________

The base dictionary view is DBA_TS_QUOTAS to fetch the specific tablespace user quota details. MAX_BYTES column value -1 means unlimited quota for the user for the particular tablespace.

DROPPED column determines whether the tablespace is dropped but the quota has been allocated to the user. 

Query with example 

Here in this example I have created one tablespace and allocated quota to test user. Then I dropped the tablespace.

advertisements

 

create tablespace user_idx datafile '/u02/oradata/CDB/user_idx01.dbf' size 500m
SQL> /

Tablespace created.

SQL> alter user test quota 200m on user_idx;

User altered.

drop tablespace user_idx including contents
SQL> /

Tablespace dropped.

set pages 1000 lines 120
col usr_name for a20
col tbs_name for a20
col Quota_allocated for a15
select 
  username          usr_name, 
  tablespace_name   tbs_name, 
  decode(max_bytes, -1, 'UNLIMITED', max_bytes/1024/1024) Quota_allocated, 
  bytes/1024        used,
  dropped 
from 
  dba_ts_quotas 
order by 
  1,2 
/

USR_NAME      TBS_NAME    QUOTA_ALLOCATED USED DRO
-------------------- -------------------- --------------- ---------- ---
APPQOSSYS      SYSAUX    UNLIMITED     0 NO
AUDSYS       SYSAUX    UNLIMITED   704 NO
DBSFWUSER      SYSAUX    UNLIMITED     0 NO
GGSYS       SYSAUX    UNLIMITED     0 NO
GSMADMIN_INTERNAL    SYSAUX    UNLIMITED   896 NO
LBACSYS       SYSTEM    UNLIMITED   320 NO
MDSYS       SYSAUX    UNLIMITED       205888 NO
MDSYS       SYSTEM    UNLIMITED     0 NO
OLAPSYS       SYSAUX    UNLIMITED     0 NO
OUTLN       SYSTEM    UNLIMITED   576 NO
TEST       USER_DATA    500      0 NO
TEST       USER_IDX    200      0 YES

12 rows selected.

_____________________________________________________________________________________________________________________

Website Stats