ORA-01950
In this post, we introduce some error patterns of ORA-01950 as below:
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.
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.