Oracle Tablespace Quota Management Script

In Oracle databases, it’s important to manage user quotas on tablespaces effectively to prevent users from consuming excessive disk space. The following scripts provide tools to report, view, and manage tablespace quotas allocated to users.

1. Tablespace Quota Details for All Users

This script reports the quota allocated for each database user and the amount of tablespace they have consumed.

set pagesize 200
set lines 200
col ownr format a20         justify c heading 'Owner' 
col name format a20         justify c heading 'Tablespace' trunc 
col qota format a12         justify c heading 'Quota (KB)' 
col used format 999,999,990 justify c heading 'Used (KB)' 
set colsep '|'
select 
  username          ownr, 
  tablespace_name   name, 
  decode(greatest(max_bytes, -1), 
    -1, 'UNLIMITED', 
    to_char(max_bytes/1024, '999,999,990') 
  )                 qota, 
  bytes/1024        used 
from 
  dba_ts_quotas 
where 
  max_bytes != 0 
  or bytes != 0 
order by 
  1, 2;
/

Explanation: This query retrieves the tablespace quotas and usage for all users, showing the tablespace name, the allocated quota (in KB), and the amount of space currently used (in KB).

Sample Output

       Owner        |     Tablespace     | Quota (KB) | Used (KB)
--------------------|--------------------|------------|------------
APPQOSSYS           |SYSAUX              |UNLIMITED   |           0
AUDSYS              |SYSAUX              |UNLIMITED   |       2,560
DBSFWUSER           |SYSAUX              |UNLIMITED   |           0
GGSYS               |SYSAUX              |UNLIMITED   |           0
GSMADMIN_INTERNAL   |SYSAUX              |UNLIMITED   |         896
HR                  |SYSAUX              |UNLIMITED   |       1,600
LBACSYS             |SYSTEM              |UNLIMITED   |         320
MDSYS               |SYSAUX              |UNLIMITED   |     172,992
MDSYS               |SYSTEM              |UNLIMITED   |           0
OLAPSYS             |SYSAUX              |UNLIMITED   |           0
OUTLN               |SYSTEM              |UNLIMITED   |         576

11 rows selected.

2. Tablespace Quota Details for a Specific User

If you need to check the tablespace quota details for a specific user, use the following script:

set pagesize 200
set lines 200
col ownr format a20         justify c heading 'Owner' 
col name format a20         justify c heading 'Tablespace' trunc 
col qota format a12         justify c heading 'Quota (KB)' 
col used format 999,999,990 justify c heading 'Used (KB)' 
set colsep '|'
select 
  username          ownr, 
  tablespace_name   name, 
  decode(greatest(max_bytes, -1), 
    -1, 'UNLIMITED', 
    to_char(max_bytes/1024, '999,999,990') 
  )                 qota, 
  bytes/1024        used 
from 
  dba_ts_quotas 
where 
  (max_bytes != 0 
   or bytes != 0) 
  and username = '&USERNAME'
order by 
  1, 2;
/

Sample Output

       Owner        |     Tablespace     | Quota (KB) | Used (KB)
--------------------|--------------------|------------|------------
        HR          |SYSAUX              |UNLIMITED   |       1,600

3. Assign Unlimited Quota to a User

To assign an unlimited tablespace quota to a user for a specific tablespace, you can use the following commands:

alter user HR quota unlimited on HR_TS; 
alter user HR quota unlimited on HR_IDX;

Explanation: This command will assign unlimited quota on the specified tablespaces (HR_TS and HR_IDX) to the user HR.

Also read:

How to Resolve ORA-01536: Space Quota Exceeded for Tablespace

These scripts can help database administrators to monitor and manage tablespace allocations effectively. Ensure that users do not exceed their allocated quotas, preventing disk space issues and system slowdowns.