Skip to content

How to Resolve ORA-01950: no privileges on tablespace

  • by

ORA-01950

Tried to create a new table or insert some data into the table, but it failed with ORA-01950.

CREATE TABLE

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 'ERPTBS'

INSERT INTO

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

Table created.

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

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 expected behavior, the error correctly protect the tablespace from allocating space for unprivileged users.

Solutions

There're two ways to solve ORA-01950.

1. Create Table or Index in Available Tabelspaces

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

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, or move the created table to the tablespace.

2. Grant Some Quota on Tablespace

Normally, you don't have to specify tablespace clause, because all new tables go to the default tablespace. Chances are, DBA forgot to grant some space on the default tablespace for you.

SQL> select default_tablespace from user_users;

DEFAULT_TABLESPACE
------------------------------
ERPTBS

Now, you should ask for DBA to grant quota on the tablespace to you or change the default tablespace for you.

Leave a Reply

Your email address will not be published.