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';


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

SQL>grant unlimited tablespace to new;


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s