Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question

2 Answers 2

up vote 7 down vote accepted

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;
share|improve this answer
    
can you please explain the command alter database default tablespace users –  Suhail Gupta Mar 11 '13 at 13:05
    
Also what does m stand for in 50m ? –  Suhail Gupta Mar 11 '13 at 13:05
    
Megabytes. It's obvious. –  Phil Mar 11 '13 at 13:07
    
On Oracle XE 11, the users tablespace already exists, so you only need to alter database default tablespace users; and alter user scott quota 50m on system; –  Andrew Spencer Nov 14 '13 at 7:55
ALTER USER <user> QUOTA 100M ON <user>;

GRANT UNLIMITED TABLESPACE TO <user>
share|improve this answer
1  
Could you explain what the OP's problem is and how this code solves it? That would make this answer much stronger. –  Nick Chammas May 13 '13 at 5:25

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.