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
3tablespace_name
4decode(max_bytes, -1, 'unlimited', ceil(max_bytes / 1024 / 1024) || 'M' ) "QUOTA"
5from   dba_ts_quotas
6here  tablespace_name not in ('TEMP')

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
 998 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 ofmax_bytes:
    • -1: If equal to -1, it indicates an unlimited quota and displays "unlimited".
    • Otherwise: It calculates the quota in Megabytes (MB) usingceil(max_bytes / 1024 / 1024), then appends "M" and assigns the result to the alias "QUOTA".
• from dba_ts_quotas: Specifies the data source, thedba_ts_quotasdata 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.
• Thedecodefunction 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 formax_bytesindicates an unlimited quota for the user in that tablespace.
• Formatting: Theformatclause ensures consistent column output for quota values.
• Data Dictionary Views: This code benefits from data dictionary views likedba_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.