How to Resolve ORA-01950: no privileges on tablespace 'USERS'

ORA-01950

In this post, we introduce some error patterns of ORA-01950 as below:

1. CREATE TABLE
2. INSERT INTO
3. ALTER TABLE

CREATE TABLE

Tried to create a new table as well as insert some data, but it failed with ORA-01950.

SQL> create table t0 as select * from big_table;
create table t0 as select * from big_table
                                 *
ERROR at line 1:
ORA-01950: no privileges on tablespace 'USERS'

We used Create Table As Select (CTAS) to create a new table along with inserting some data at the same time. Without specifying tablespace clause, the table goes to user's default tablespace.

INSERT INTO

Tried to insert some data into an empty table, but it failed with ORA-01950.

SQL> create table t1 (c1 number) tablespace users;

Table created.

SQL> insert into t1 values (1);
insert into t1 values (1)
            *
ERROR at line 1:
ORA-01950: no privileges on tablespace 'USERS'

As you can see, we created an empty table in the specified tablespace successfully, but we cannot insert data into it.

ALTER TABLE

Tried to move the table to another place, but it failed with ORA-01950.

SQL> alter table t2 move tablespace users online parallel 8;
alter table t2 move tablespace users online parallel 8
            *
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01950: no privileges on tablespace 'USERS'

ORA-01950 means that the user does not have any quota privilege to allocate extent on the tablespace, even though the table could have been created in that tablespace.

This is an expected response, the error correctly protect the tablespace from allocating space for unprivileged users.

Solutions

There're several ways to solve ORA-01950.

1. Grant Some Quota on Tablespace
2. Move Table and Index to Available Tabelspace
3. Change Default Tablespace

Grant Some Quota on Tablespace

To solve ORA-01950, you should ask for DBA to grant quota on said tablespace to you, which was specified in the error message.

Move Table and Index to Available Tabelspace

As a developer, you should check what tablespaces you can use by querying USER_TS_QUOTAS.

SQL> column tablespace_name format a20;
SQL> column "QUOTA(GB)" format a10;
SQL> select tablespace_name, case max_bytes when -1 then 'UNLIMITED' else to_char(max_bytes/1024/1024/1024) end "QUOTA(GB)" from user_ts_quotas;

TABLESPACE_NAME      QUOTA(GB)
-------------------- ----------
EXAMPLE              UNLIMITED
FINAPP               UNLIMITED

In the above, 2 tablespaces are available to be used, you should create the table in any one of usable tablespaces in the first place, or move the created table to the tablespace.

If you still got the error after moving table to another tablespace. Most likely, it's an index issue, you should move dependent indexes as well.

For CTAS statements, you may CTAS the new table to another available tablespace.

Change Default Tablespace

Normally, you don't have to specify tablespace clause while creating the table, because all new tables go to user's default tablespace.

To check what your default tablespace is, you may query USER_USERS.

SQL> select default_tablespace from user_users;

DEFAULT_TABLESPACE
------------------------------
USERS

Chances are, DBA forgot to grant some space on the default tablespace for you. You may change user's default permanent tablespace to any one of available tablespaces.