How to Check User Quota on Oracle Tablespace-DBA_TS_QUOTAS

_____________________________________________________________________________________________________________________

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.

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.

_____________________________________________________________________________________________________________________

0 comments:

Post a Comment

 

acehints.com Copyright 2011-19 All Rights Reserved | Site Map | Contact | Disclaimer | Google