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'

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

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'

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

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. Move Table and Index to Available Tabelspaces

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.

2. Grant Some Quota on Tablespace

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

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

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. Required fields are marked *