PROBLEM:

While creating a table using CTAS, got an error, ORA-01950: no privileges on tablespace ‘USERS’.

SQL> create table DBACLASS.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='DBACLASS';

no rows selected.

2. Grant some QUOTA to the user.

SQL> alter user dbaclass 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='DBACLASS';


USERNAME         TABLESPACE_NAME                BYTES/1024/1024/1024 MAX_BYTES/1024/1024/1024
---------------- ------------------------------ -------------------- ------------------------
DBACLASS         USERS                                             0                        5 

Now we user DBACLASS has 5G quota on tablespace USERS. Let’s run the same create statement.

SQL> SQL> create table DBACLASS.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='DBACLASS';

USERNAME         TABLESPACE_NAME                BYTES/1024/1024/1024 MAX_BYTES/1024/1024/1024
---------------- ------------------------------ -------------------- ------------------------
DBACLASS         USERS                                    .005493164                        5