
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!!!