Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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, ...

share|improve this question
    
What version of Oracle? Also, did kainaw logout & back in again after the grant was executed? (You need to). –  FreshPhilOfSO Sep 9 '13 at 19:11
    
Updated question to answer these questions. –  kainaw Sep 9 '13 at 19:18
    
Does the schema owner i have privs on the tablespace USERS, because it needs them rather than the user kainaw needing them - kainaw doesn`t own the table, i does. –  FreshPhilOfSO Sep 9 '13 at 19:31
    
Yes i has all privileges on i.test. However, i is not a real user. i is being used as a schema for tables that all users will use. So, I'm trying to insert into i.test from a user other than i. –  kainaw Sep 9 '13 at 19:34
1  
As I said, user i owns the table, therefore user i needs to be granted a quota on the USERS tablespace. i IS still a user that exists in the database. In Oracle Schema=User (at least until 12c came along). That`s the way it works - the owner of the table needs the grants, not the user that is actually inserting the data (because that makes no sense!). –  FreshPhilOfSO Sep 9 '13 at 19:36
show 2 more comments

1 Answer

up vote 1 down vote accepted

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.