Ошибка ora 01950

You can get this effect if your user had either the RESOURCE or UNLIMITED TABLESPACE role assigned at the point the tables were created; but that has since been revoked, and the table is now trying to allocate a new extent. Your user has not had a quota explicitly set for the tablespace; if it had then you’d be seeing «ORA-01536: space quota exceeded for tablespace ‘USERS'» instead, even if the quota had subsequently been removed by setting it to zero.

To see the effect:

-- grant unlimited tablespace to user;

create table t42 (id number) tablespace users;

Table t42 created.

insert into t42
select level as id
from dual
connect by level < 1000;

1,999 rows inserted.

select extents from user_segments where segment_name = 'T42';

   EXTENTS
----------
         1 

-- revoke unlimited tablespace from user;

At this point I can still insert data:

insert into t42 values (2000);

1 rows inserted.

But if I insert enough rows to require a second extent to be allocated, it fails with this error:

insert into t42
select level + 2000 as id
from dual
connect by level < 2000;

Error report -
SQL Error: ORA-01950: no privileges on tablespace 'USERS'
01950. 00000 -  "no privileges on tablespace '%s'"
*Cause:    User does not have privileges to allocate an extent in the
           specified tablespace.
*Action:   Grant the user the appropriate system privileges or grant the user
           space resource on the tablespace.

Presumably your DBA has been doing some housekeeping of privileges, perhaps revoking RESOURCE since it’s deprecated.

As mentioned in comments, your DBA needs to grant you some space on the tablespace, with a specific size or (to match what you had before) no limit:

grant quota unlimited on users to myuser;

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;

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;

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;

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.

ORA-01950: no privileges on tablespace | Tips and Solution | Oracle Database

ORA-01950: no privileges on tablespace

ORA-01950 is a normal error with a easy solution. The error simply tells you that the user who owns the object (Let’s say a table) is not having privileges on the tablespace mentioned in the error description.

You can use the below mentioned query to find the quota limits for all the users in your database along with the tablespace name. (this will only display the list of USERS will limited quota).

SQL> col username for a30;
SQL> set lines 150
SQL> set pages 100
SQL> select * from dba_ts_quotas;

TABLESPACE_NAME USERNAME BYTES MAX_BYTES BLOCKS MAX_BLOCKS DRO
—————————— —————————— ———- ———- ———- ———- —
SYSAUX AUDSYS 1572864 -1 192 -1 NO
SYSAUX GSMADMIN_INTERNAL 1441792 104857600 176 12800 NO
SYSAUX APPQOSSYS 0 -1 0 -1 NO
SYSAUX OLAPSYS 0 -1 0 -1 NO
SYSAUX FLOWS_FILES 0 -1 0 -1 NO
EXAMPLE OE 10420224 -1 1272 -1 NO

OR to get the quota information for some particular USER, run

SQL> select * from dba_ts_quotas where username like ‘OE’;

TABLESPACE_NAME USERNAME BYTES MAX_BYTES BLOCKS MAX_BLOCKS DRO
—————————— —————————— ———- ———- ———- ———- —
EXAMPLE OE 10420224 -1 1272 -1 NO

Now to resolve the error, please use any option from the below mentioned queries depending upon your requirement,

1. Provide unlimited quota on any TABLESPACE to any USER which simply please I am allowing that USER to use as much space as it wants in any tablespace.

GRANT UNLIMITED TABLESPACE TO HR;

2. Provide a limited amount of quota on some particular tablespace.

alter user HR quota unlimited on USERS;

I hope this helps !!