PROBLEM:
While creating a table using CTAS, got an error, ORA-01950: no privileges on tablespace ‘USERS’.
SQL> create table FRANCE.EMPLO as select * from user_objects;
create table EMPLO as select * from user_objects
*
ERROR at line 1:
ORA-01950: no privileges on tablespace ‘USERS’
SOLUTION:
This error comes, when the user the user doesn’t have tablespace quota.
1. Check the tablespace quota.
select username,tablespace_name,bytes/1024/1024/1024, MAX_BYTES/1024/1024/1024 from dba_ts_quotas where username='FRANCE'; no rows selected.
2. Grant some QUOTA to the user.
SQL> alter user FRANCE quota 5G on USERS; User altered. SQL> select username,tablespace_name,bytes/1024/1024/1024, MAX_BYTES/1024/1024/1024 from dba_ts_quotas where username='FRANCE'; USERNAME TABLESPACE_NAME BYTES/1024/1024/1024 MAX_BYTES/1024/1024/1024 -------- --------------- ----------------- ----------------- FRANCE USERS 0 5
Now we user FRANCE has 5G quota on tablespace USERS. Let’s run the same create statement.
SQL> create table FRANCE.EMPLO as select * from user_objects; Table created.
It worked. Now, check how much quota has been used.
SQL> select username,tablespace_name,bytes/1024/1024/1024, MAX_BYTES/1024/1024/1024 from dba_ts_quotas where username='FRANCE'; USERNAME TABLESPACE_NAME BYTES/1024/1024/1024 MAX_BYTES/1024/1024/1024 -------- -------------- ------------------- -------------------- FRANCE USERS .005493164 5
Catch Me On:- Hariprasath Rajaram Telegram:https://t.me/joinchat/I_f4DkeGfZuxgMIoJSpQZg LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/ Facebook:https://www.facebook.com/HariPrasathdba FB Group:https://www.facebook.com/groups/894402327369506/ FB Page: https://www.facebook.com/dbahariprasath/? Twitter: https://twitter.com/hariprasathdba
