Understanding User Quotas in Oracle Database
How to Query User Quotas in Oracle Database
The code below retrieves and displays information about user quotas on all tablespaces excluding the temporary tablespace (TEMP
) on a Oracle database.
Sample SQL Command
1col quota format a10
2select username
3, tablespace_name
4, decode(max_bytes, -1, 'unlimited'
5 , ceil(max_bytes / 1024 / 1024) || 'M' ) "QUOTA"
6from dba_ts_quotas
7where tablespace_name not in ('TEMP')
8/
Sample Oracle Output:
1USERNAME TABLESPACE_NAME QUOTA
2-------------------- ---------------------------------------- ----------
3LARRY LARRY 50M
4ODM_REPOSITORY ODM unlimited
5PERFSTAT TOOLS 500M
6CABBOY USERS unlimited
7ORA_IT ORAIT unlimited
8
98 rows selected.
10SQL>
Purpose
This code retrieves and displays information about user quotas on all tablespaces excluding the temporary tablespace (TEMP
).
Breakdown:
col quota format a10
: This line formats the "QUOTA" column to display a maximum of 10 characters right-aligned.select username
: Selects the username of the user associated with the quota.tablespace_name
: Selects the name of the tablespace the quota applies to.decode(max_bytes, -1, 'unlimited', ceil(max_bytes / 1024 / 1024) || 'M') AS "QUOTA"
:- This line checks the value of
max_bytes
:- -1: If equal to -1, it indicates an unlimited quota and displays "unlimited".
- Otherwise: It calculates the quota in Megabytes (MB) using
ceil(max_bytes / 1024 / 1024)
, then appends "M" and assigns the result to the alias "QUOTA".
- This line checks the value of
from dba_ts_quotas
: Specifies the data source, thedba_ts_quotas
data dictionary view holding tablespace quota information.where tablespace_name not in ('TEMP')
: Filters out records for the temporary tablespace (TEMP
), as temporary objects wouldn't have explicit quotas./
: Terminates the SQL statement.
Key Points:
- This code provides a quick overview of user quotas across all tablespaces except the temporary one.
- The
decode
function is used to handle both unlimited and limited quotas and display them in a user-friendly format. - It utilizes data dictionary views for efficient retrieval of information.
Insights and Explanations:
- Unlimited Quotas: A value of -1 for
max_bytes
indicates an unlimited quota for the user in that tablespace. - Formatting: The
format
clause ensures consistent column output for quota values. - Data Dictionary Views: This code benefits from data dictionary views like
dba_ts_quotas
, which simplify complex queries.
Additional Notes:
- This code can be modified to include more information about quotas, such as quota type or the percentage of quota already used.
- Consider filtering the output based on specific users or tablespaces if needed.