ORA-01950 – GIVE USER QUOTA TO TABLESPACE

Learn by brain,understand by heart and apply by hands

We created new tablespace but not given quota for the user. Would that user be able to create extent on that tablespace? Let us see!!

SQL> create tablespace newtbs datafile '/u01/oracle/oradata/new01.dbf' size 5G;

SQL>create user new identified by password;

SQL> grant connect,resource to new;

SQL>create table newtab (emp_id number,emp_name varchar(10));

SQL>insert into newtab values(1,'xyz');

We get this error.

ORA-01952 : system privileges not granted to 'tablespace'

Check if the tablespace exist under user ‘new’

select tablespace_name,
max_bytes/1024/1024/1024 "quota_in_gb"
from dba_ts_quotas
where username = 'new';

Solution:

SQL>alter user new quota 500m on newtbs; (unlimited also can be specified)

SQL>grant unlimited tablespace to new;

Thanks!!!

Leave a Reply