Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
While creating a table got ORA-01950: no privileges on tablespace ‘USERS’.
1 2 3 4 5 | SQL> create table ABUZER.SAMPLE_TBL as select * from user_objects; create table SAMPLE_TBL 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.
1 2 3 | select username,tablespace_name,bytes/1024/1024/1024,MAX_BYTES/1024/1024/1024 from dba_ts_quotas where username='ABUZER'; no rows selected. |
Grant some QUOTA to the user.
1 2 3 4 5 6 7 8 9 10 | SQL> alter user ABUZER quota UNLIMITED on USERS; User altered. SQL> select username,tablespace_name,bytes/1024/1024/1024,MAX_BYTES/1024/1024/1024 from dba_ts_quotas where username='ABUZER'; USERNAME TABLESPACE_NAME BYTES/1024/1024/1024 MAX_BYTES/1024/1024/1024 ---------------- ------------------------------ -------------------- ------------------------ ABUZER USERS 0 UNLIMITED |
With QUOTA
1 2 3 4 5 6 7 8 9 10 | SQL> alter user ABUZER quota 1G on USERS; User altered. SQL> select username,tablespace_name,bytes/1024/1024/1024,MAX_BYTES/1024/1024/1024 from dba_ts_quotas where username='ABUZER'; USERNAME TABLESPACE_NAME BYTES/1024/1024/1024 MAX_BYTES/1024/1024/1024 ---------------- ------------------------------ -------------------- ------------------------ ABUZER USERS 0 1G |
