“ORA-01950: no privileges on tablespace 'SYSTEM' ” : What error is it?

Question

Earlier I created a user :

SQL>create user suhail identified by password
SQL>User created.
SQL>Grant create session,create table to suhail;
SQL>Grant Succeeded.

but when I login as suhail and try to create a table I get an error saying ORA-01950: no privileges on tablespace 'SYSTEM' . What error is it ? Why does it occur ?

---

SQL> connect suhail
Enter password:
Connected.
SQL> create table suhail_table(name char(10));
create table suhail_table(name char(10))
*
ERROR at line 1:
ORA-01950: no privileges on tablespace 'SYSTEM'

I am using Oracle Database 11g Express Edition Release 11.2.0.2.0

Answer 1

This error says that the user doesn't have quota on tablespace SYSTEM which is set as the default persistent tablespace. You can assign a user the quota like this:

sql> alter user scott quota 50m on system;

Here, 50m means that the user quota on the SYSTEM tablespace is 50 mebibytes. You can also set the quota to unlimited.

However it is a bad practice to store user and application data in SYSTEM tablespace as it causes data fragmentation and thus degrades performance. So I would recommend you to create a new permanent tablespace:

sql> create smallfile tablespace users datafile '/u01/app/oracle/oradata/ORCL/users.dbf' size 10g;

And then set it as the default database permanent tablespace so that objects created by the users go into that default tablespace:

sql> alter database default tablespace users;

Answer 2

ALTER USER <user> QUOTA 100M ON <user>;

GRANT UNLIMITED TABLESPACE TO <user>