Skip to content
Oracle DBA Scripts & Database Utilities

Tablespace Quota Analysis (dtsquota.sql)

What This Script Does

Section titled “What This Script Does”

This script analyzes tablespace quotas for database users by:

  • Showing current space usage by user and tablespace
  • Displaying quota limits and available space
  • Calculating remaining quota availability
  • Providing totals grouped by tablespace
  • Supporting filtering by user and tablespace patterns

Script

Section titled “Script”
rem  dtsquota.sql
rem
rem  linesize = 73
rem
ttitle 'Tablespace Quotas'
rem
col tablespace_name format a15 heading 'TABLESPACE'
col username format a12 heading 'USER NAME'
col kbytes format 999,999,999 heading 'BYTES|(K)'
col max_bytes format 9,999,999 heading 'MAX|BYTES|(K)'
col blocks format 999,999 heading 'BLOCKS'
col max_blocks format 999,999 heading 'MAX|BLOCKS'
col avail_blocks format 999,999 heading 'AVAIL|BLOCKS'
rem
break on report on tablespace_name skip 1
compute sum of kbytes max_bytes blocks max_blocks -
     on report tablespace_name
rem
select tablespace_name, username,
       bytes/1024 kbytes, greatest( max_bytes/1024, 0 ) max_bytes,
       blocks, greatest( max_blocks, 0 ) max_blocks,
       decode( max_blocks, 0, 0, -1, 0, max_blocks - blocks ) avail_blocks
  from sys.dba_ts_quotas
 where username like upper('&owner')
   and tablespace_name like upper('&tablespace')
 order by tablespace_name, username;

Usage

Section titled “Usage”
SQL> @dtsquota.sql
Enter value for owner: SCOTT
Enter value for tablespace: USERS

Parameters

Section titled “Parameters”
  • owner: Username pattern to filter (use % for all users)
  • tablespace: Tablespace name pattern (use % for all tablespaces)

Required Privileges

Section titled “Required Privileges”
  • SELECT on SYS.DBA_TS_QUOTAS

Sample Output

Section titled “Sample Output”
Tablespace Quotas


TABLESPACE      USER NAME           BYTES   MAX    BLOCKS    MAX   AVAIL
                                       (K) BYTES       BLOCKS BLOCKS
                                           (K)
--------------- ------------ ------------ ------- ------- ------- -------
USERS           SCOTT              12,345  50,000   1,543  6,250   4,707
                HR                  8,765  25,000   1,096  3,125   2,029
                SALES              23,456 100,000   2,932 12,500   9,568
                                -------- -------  ------ ------
sum                               44,566 175,000   5,571 21,875


TEMP            SCOTT               2,345      -1     293     -1       0
                HR                  1,234      -1     154     -1       0
                                --------  ------  ------ ------
sum                                3,579      -1     447     -1


SYSAUX          SYSTEM            245,678       0  30,710      0       0
                                --------  ------  ------ ------
sum                              245,678       0  30,710      0

Key Output Columns

Section titled “Key Output Columns”
  • TABLESPACE: Tablespace name
  • USER NAME: Database username
  • BYTES (K): Current space used in kilobytes
  • MAX BYTES (K): Maximum quota in kilobytes
  • BLOCKS: Current blocks used
  • MAX BLOCKS: Maximum blocks allowed
  • AVAIL BLOCKS: Available blocks remaining

Understanding Quota Values

Section titled “Understanding Quota Values”

Quota Types

Section titled “Quota Types”
  • Specific limit: Positive number showing maximum allowed
  • UNLIMITED: -1 indicates unlimited quota
  • No quota: 0 means no space allocation allowed

Usage Calculations

Section titled “Usage Calculations”
  • Available space: max_blocks - current_blocks
  • Usage percentage: (current_blocks / max_blocks) * 100
  • Unlimited quota: Shows -1 for max values

Common Use Cases

Section titled “Common Use Cases”

  1. Space Management

    • Monitor user space consumption
    • Identify users approaching quota limits
    • Plan quota adjustments

  2. Capacity Planning

    • Track growth patterns by user
    • Forecast tablespace growth
    • Optimize space allocation

  3. Security Administration

    • Audit user space privileges
    • Validate quota policies
    • Prevent space abuse

Quota Management

Section titled “Quota Management”

Check users near quota limits:

Section titled “Check users near quota limits:”
SELECT username, tablespace_name,
       ROUND((bytes/1024)/GREATEST(max_bytes/1024,1)*100,1) pct_used
FROM dba_ts_quotas
WHERE max_bytes > 0  -- Exclude unlimited
AND (bytes/max_bytes) > 0.8  -- Over 80% used
ORDER BY pct_used DESC;

Find unlimited quotas:

Section titled “Find unlimited quotas:”
SELECT username, tablespace_name, 'UNLIMITED' quota
FROM dba_ts_quotas
WHERE max_bytes = -1
ORDER BY tablespace_name, username;

Grant/modify quotas:

Section titled “Grant/modify quotas:”
-- Grant specific quota
ALTER USER scott QUOTA 100M ON users;


-- Grant unlimited quota
ALTER USER scott QUOTA UNLIMITED ON users;


-- Revoke quota
ALTER USER scott QUOTA 0 ON users;

Troubleshooting Quota Issues

Section titled “Troubleshooting Quota Issues”

Common Error Messages

Section titled “Common Error Messages”
  • ORA-01536: Space quota exceeded
  • ORA-01950: No privileges on tablespace

Resolution Steps

Section titled “Resolution Steps”
  1. Increase quota: Raise user’s tablespace quota
  2. Clean up data: Remove unnecessary objects
  3. Move objects: Relocate to different tablespace
  4. Add space: Extend tablespace capacity

Monitoring Recommendations

Section titled “Monitoring Recommendations”

Daily Checks

Section titled “Daily Checks”
  • Users > 90% of quota
  • Recently modified quotas
  • Failed operations due to quota

Weekly Reviews

Section titled “Weekly Reviews”
  • Quota usage trends
  • Tablespace capacity vs. quotas
  • User space growth patterns

Alerts Setup

Section titled “Alerts Setup”
-- Users over 95% quota usage
SELECT username || ' is at ' ||
       ROUND(bytes/GREATEST(max_bytes,1)*100,1) ||
       '% quota usage in ' || tablespace_name
FROM dba_ts_quotas
WHERE max_bytes > 0
AND bytes/max_bytes > 0.95;
Section titled “Related Scripts”