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). –  Phil 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 doesnt own the table, i` does. –  Phil 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!). –  Phil Sep 9 '13 at 19:36

2 Answers 2

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

In case this doesn't work, take a look at your table definition... specifically indexes. Please take a look at this post on my blog for details.Check table definition

Thanks, Jason

share|improve this answer
1  
You need to note in your answer that you wrote the blog entry you link to. –  Max Vernon May 15 at 18:26
    
@Max Vernon: I think "my blog" is clear enough –  miracle173 May 16 at 6:12

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.