ORA-01950: no privileges on tablespace 'USERS'

The error ORA-01950: no privileges on tablespace 'USERS' means that the user does not have quota on the USERS tablespace to create tables, indexes, or store data.

This is very common when creating a new user in Oracle.

---

✅ How to Fix ORA-01950

You must give the user quota on the tablespace, or grant unlimited quota.

---

Option 1: Give Specific Quota (Recommended)

ALTER USER your_user QUOTA 200M ON users;

This allows the user to use 200 MB in the USERS tablespace.

You can increase quota anytime.

---

Option 2: Give Unlimited Quota on USERS Tablespace

ALTER USER your_user QUOTA UNLIMITED ON users;

This allows unlimited space usage only in USERS tablespace
(not dangerous like UNLIMITED TABLESPACE system privilege).

---

Option 3: Change User’s Default Tablespace

If USERS tablespace is restricted, change default tablespace to another one:

ALTER USER your_user DEFAULT TABLESPACE example;
ALTER USER your_user QUOTA UNLIMITED ON example;

---

Option 4: Check Current Quotas

SELECT tablespace_name, max_bytes
FROM dba_ts_quotas
WHERE username = 'YOUR_USER';

---

✔ Why the Error Occurs

It appears when:

• User tries to create a table
• User tries to insert data
• User tries to create an index
• And they have no quota on the tablespace where data must be stored

Oracle blocks the operation with ORA-01950.

---

📌 Example

If you created a user like this:

CREATE USER test IDENTIFIED BY test;
GRANT CREATE SESSION, CREATE TABLE TO test;

Then try to create a table:

CREATE TABLE a (id NUMBER);

You will get:

ORA-01950: no privileges on tablespace 'USERS'

Solution:

ALTER USER test QUOTA UNLIMITED ON users;