Wednesday, December 28, 2011

ORA-01950

I was in a hurry to get something finished today. I created a user and specified its default tablespace. I logged in as that user then tried to create a table. This failed with an ORA-01950 as the user had no quota on its default tablespace. I granted the user a quota of 5 megabytes and it was then able to create the table. You can see what I mean in the example, which was tested on an Oracle 9 database:
 
SQL> conn / as sysdba
Connected.
SQL> create user andrew
  2  identified by reid
  3  default tablespace user_data
  4  /
 
User created.
 
SQL> grant create session, create table
  2  to andrew
  3  /
 
Grant succeeded.
 
SQL> conn andrew/reid
Connected.
SQL> create table my_table
  2  (col1 varchar2(10))
  3  /
create table my_table
*
ERROR at line 1:
ORA-01950: no privileges on tablespace 'USER_DATA'
 
SQL> conn / as sysdba
Connected.
SQL> alter user andrew
  2  quota 5m on user_data
  3  /
 
User altered.
 
SQL> conn andrew/reid
Connected.
SQL> create table my_table
  2  (col1 varchar2(10))
  3  /
 
Table created.
 
SQL>

No comments: