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".
  • from dba_ts_quotas: Specifies the data source, the dba_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.

Posts in this series