1

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

  • 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
6

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.

1

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

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

This is very common error which comes due to insufficient privileges on tablespace. Just try this.

SQL>select USERNAME, DEFAULT_TABLESPACE from dba_users where USERNAME='XXXXX';
SQL> alter user USER_NAME DEFAULT TABLESPACE TABLESPC_NAME quota unlimited on TABLESPC_NAME;

ORA-01950

1

It is because no privilege on tablespace. Grant tablespace quota to the user to fix this:

SQL> alter user quota unlimited on tablespace_name;

SQL> GRANT UNLIMITED TABLESPACE TO username;

Detailed on ora-01950

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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