HiveBrain v1.2.0
Get Started
← Back to all entries
patternModerate

Oracle no privileges on tablespace USERS

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
privilegesoracleuserstablespace

Problem

I have a brand new Oracle database that is giving the error:

ORA-01950: no privileges on tablespace 'USERS'


I have done:

alter user kainaw quota 100M on 'USERS';
grant unlimited tablespace to kainaw;


Still, a single insert returns that error. Other than disk quota, what else causes the "no privileges on tablespace 'USERS'" error?

UPDATE:

Oracle version is 11.2.0.3.0 (11g). I am logging in from the command prompt on the server. So, I alter user kainaw as sysdba. Then, I logout and login a user kainaw to test:

insert into i.test values (1);


Note: i.test is a table with only a number field. I get the error above. I logout as kainaw, login as sysdba, play with permissions, logout, login, test, error, logout, login, ...

Solution

You are granting the privileges to the incorrect user.

The schema owner i owns the table, and is therefore the user that needs to be granted the relevant permissions on the tablespace.

Context

StackExchange Database Administrators Q#49549, answer score: 13

Revisions (0)

No revisions yet.