Kulsoom Patel Kulsoom Patel - 5 months ago 25
SQL Question

ORA-01950: no privileges on tablespace 'USERS' as the quota is on another table

I'm currently getting a

ORA-01950: no privileges on tablespace 'USERS'
as my quota is on another table.

I've executed the query:

SELECT * FROM USER_TS_QUOTAS;


I've got the following results:

TABLESPACE_NAME | BYTES | MAX_BYTES | MAX_BLOCKS | DROPPED
DATA 0 -1 0 NO


My user has an unlimited quota however when executing an insert I'm unable to do this as it's looking at the USERS table. Any way to fix this?

UPDATE:

The default tablespace was users. The query below confirmed this.

SELECT PROPERTY_VALUE
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME = 'DEFAULT_PERMANENT_TABLESPACE';


The solution to this problem is to change the tablespace to where I have the quota. Thanks to Alex Poole.

Answer

You either need to recreate you table in the DATA tablespace:

drop table your_table;
create table your_table (...) tablespace data;

Or if you actually want it in the USERS tablespace, your DBA will need to allow you to use space in that tablespace as well:

alter user your_user quota unlimited on users;
Comments