ORA-01950 No Privileges on Tablespace

ORA-01950: No Privileges on Tablespace

Error Overview

Error Text: ORA-01950: no privileges on tablespace 'tablespace_name'

The ORA-01950 error occurs when a user attempts to create or extend a segment (table, index, etc.) in a tablespace where they have no space quota allocated. This is a common error during application deployment or when users try to create objects without proper quota grants.

Common Causes

1. Missing Tablespace Quota

• User never granted quota on target tablespace
• Quota exhausted (reached allocated limit)
• User creating objects in wrong tablespace

2. Schema Configuration Issues

• New users not given proper quotas
• Default tablespace without quota
• Application deployment to wrong schema

3. Privilege Confusion

• Assuming CREATE TABLE implies quota
• RESOURCE role no longer grants UNLIMITED TABLESPACE
• Quota not inherited through roles

4. Storage Clause Problems

• Table explicitly specifying tablespace
• Index creation on different tablespace
• LOB segments on separate tablespace

Diagnostic Queries

Check User Quotas

-- All quotas for a user
SELECT
  tablespace_name,
  bytes/1024/1024 as used_mb,
  DECODE(max_bytes, -1, 'UNLIMITED', max_bytes/1024/1024) as quota_mb
FROM dba_ts_quotas
WHERE username = UPPER('&username')
ORDER BY tablespace_name;

-- Check if user has UNLIMITED TABLESPACE
SELECT * FROM dba_sys_privs
WHERE grantee = UPPER('&username')
  AND privilege = 'UNLIMITED TABLESPACE';

-- Check through roles
SELECT DISTINCT privilege
FROM dba_sys_privs
WHERE grantee IN (
  SELECT granted_role
  FROM dba_role_privs
  WHERE grantee = UPPER('&username')
)
AND privilege = 'UNLIMITED TABLESPACE';

Check User’s Default Tablespace

-- User's default and temp tablespaces
SELECT
  username,
  default_tablespace,
  temporary_tablespace,
  account_status
FROM dba_users
WHERE username = UPPER('&username');

-- Compare with quota
SELECT
  u.username,
  u.default_tablespace,
  NVL(q.max_bytes, 0) as quota_bytes
FROM dba_users u
LEFT JOIN dba_ts_quotas q
  ON u.username = q.username
  AND u.default_tablespace = q.tablespace_name
WHERE u.username = UPPER('&username');

Check Tablespace Space

-- Tablespace free space
SELECT
  tablespace_name,
  ROUND(SUM(bytes)/1024/1024, 2) as free_mb
FROM dba_free_space
WHERE tablespace_name = UPPER('&tablespace_name')
GROUP BY tablespace_name;

-- Tablespace usage summary
SELECT
  t.tablespace_name,
  ROUND(t.bytes/1024/1024, 2) as total_mb,
  ROUND(f.free_bytes/1024/1024, 2) as free_mb,
  ROUND((t.bytes - f.free_bytes)/1024/1024, 2) as used_mb
FROM (
  SELECT tablespace_name, SUM(bytes) as bytes
  FROM dba_data_files
  GROUP BY tablespace_name
) t
LEFT JOIN (
  SELECT tablespace_name, SUM(bytes) as free_bytes
  FROM dba_free_space
  GROUP BY tablespace_name
) f ON t.tablespace_name = f.tablespace_name
ORDER BY t.tablespace_name;

Resolution Steps

1. Grant Specific Quota

-- Grant specific quota (in MB or KB)
ALTER USER username QUOTA 100M ON tablespace_name;

-- Grant quota in different units
ALTER USER username QUOTA 1G ON tablespace_name;     -- 1 Gigabyte
ALTER USER username QUOTA 500M ON tablespace_name;   -- 500 Megabytes
ALTER USER username QUOTA 10240K ON tablespace_name; -- 10 Megabytes

2. Grant Unlimited Quota on Tablespace

-- Unlimited quota on specific tablespace
ALTER USER username QUOTA UNLIMITED ON tablespace_name;

-- Example for data and index tablespaces
ALTER USER app_user QUOTA UNLIMITED ON app_data;
ALTER USER app_user QUOTA UNLIMITED ON app_indexes;

3. Grant UNLIMITED TABLESPACE Privilege

-- System-wide unlimited tablespace (use cautiously)
GRANT UNLIMITED TABLESPACE TO username;

-- Revoke if needed later
REVOKE UNLIMITED TABLESPACE FROM username;

4. Set Proper Default Tablespace

-- Change user's default tablespace
ALTER USER username DEFAULT TABLESPACE new_tablespace;

-- Verify and grant quota on new default
ALTER USER username QUOTA UNLIMITED ON new_tablespace;

5. Batch Quota Grant Script

-- Grant quota to multiple users
BEGIN
  FOR rec IN (
    SELECT username
    FROM dba_users
    WHERE username LIKE 'APP_%'
      AND account_status = 'OPEN'
  ) LOOP
    EXECUTE IMMEDIATE
      'ALTER USER ' || rec.username || ' QUOTA UNLIMITED ON APP_DATA';
    DBMS_OUTPUT.PUT_LINE('Granted quota to: ' || rec.username);
  END LOOP;
END;
/

Prevention Strategies

1. User Creation Template

-- Standard user creation with proper quotas
CREATE USER app_user IDENTIFIED BY password
  DEFAULT TABLESPACE app_data
  TEMPORARY TABLESPACE temp
  QUOTA UNLIMITED ON app_data
  QUOTA 100M ON app_indexes
  ACCOUNT UNLOCK;

-- Grant necessary privileges
GRANT CREATE SESSION TO app_user;
GRANT CREATE TABLE TO app_user;
GRANT CREATE VIEW TO app_user;
GRANT CREATE SEQUENCE TO app_user;

2. Create User Provisioning Procedure

CREATE OR REPLACE PROCEDURE provision_app_user(
  p_username VARCHAR2,
  p_password VARCHAR2,
  p_data_tablespace VARCHAR2 DEFAULT 'APP_DATA',
  p_index_tablespace VARCHAR2 DEFAULT 'APP_INDEXES'
) AS
BEGIN
  -- Create user
  EXECUTE IMMEDIATE 'CREATE USER ' || p_username ||
    ' IDENTIFIED BY "' || p_password || '"' ||
    ' DEFAULT TABLESPACE ' || p_data_tablespace ||
    ' TEMPORARY TABLESPACE TEMP';

  -- Grant quotas
  EXECUTE IMMEDIATE 'ALTER USER ' || p_username ||
    ' QUOTA UNLIMITED ON ' || p_data_tablespace;
  EXECUTE IMMEDIATE 'ALTER USER ' || p_username ||
    ' QUOTA 500M ON ' || p_index_tablespace;

  -- Grant application role
  EXECUTE IMMEDIATE 'GRANT APP_USER_ROLE TO ' || p_username;

  DBMS_OUTPUT.PUT_LINE('User ' || p_username || ' created successfully');
END;
/

3. Monitor Quota Usage

-- Users approaching quota limits
SELECT
  username,
  tablespace_name,
  bytes/1024/1024 as used_mb,
  max_bytes/1024/1024 as quota_mb,
  ROUND(bytes/max_bytes * 100, 2) as pct_used
FROM dba_ts_quotas
WHERE max_bytes > 0
  AND bytes/max_bytes > 0.8  -- Over 80% used
ORDER BY bytes/max_bytes DESC;

-- Create alert for quota usage
CREATE OR REPLACE PROCEDURE check_quota_usage AS
BEGIN
  FOR rec IN (
    SELECT username, tablespace_name,
           ROUND(bytes/max_bytes * 100, 2) as pct_used
    FROM dba_ts_quotas
    WHERE max_bytes > 0 AND bytes/max_bytes > 0.9
  ) LOOP
    DBMS_OUTPUT.PUT_LINE('WARNING: ' || rec.username ||
      ' at ' || rec.pct_used || '% quota on ' || rec.tablespace_name);
  END LOOP;
END;
/

Understanding Quotas vs Privileges

What	Purpose
CREATE TABLE	Permission to create tables (DDL privilege)
QUOTA ON TABLESPACE	Space allocation to store objects
UNLIMITED TABLESPACE	System privilege - unlimited space everywhere
DEFAULT TABLESPACE	Where objects go without explicit TABLESPACE clause

Note: Having CREATE TABLE privilege does NOT automatically grant quota. Both are required.

Related Errors

• ORA-01536 - Space quota exceeded for tablespace
• ORA-01653 - Unable to extend table
• ORA-01031 - Insufficient privileges
• ORA-00959 - Tablespace does not exist

Emergency Response

Quick Quota Grant

-- Immediate fix - grant unlimited quota
ALTER USER problem_user QUOTA UNLIMITED ON target_tablespace;

-- Verify fix
SELECT tablespace_name, max_bytes
FROM dba_ts_quotas
WHERE username = 'PROBLEM_USER';

Check What User Needs

-- Find where user is trying to write
SELECT
  segment_name,
  segment_type,
  tablespace_name
FROM dba_segments
WHERE owner = UPPER('&username')
ORDER BY bytes DESC
FETCH FIRST 10 ROWS ONLY;