ORA-01950

The error message ORA-01950 should be easy to solve, but perhaps you forgot the indexes?

ORA-01950

ORA-01950 no privileges on tablespace … usually has a couple obvious solutions. If the tablespace in the error message is wrong like SYSTEM, the default tablespace must be changed for the user. Change it with something like this:

alter user luser default tablespace users quota 1g on users;

If the tablespace is correct, the user may be lacking a quota on the tablespace. The fix is almost the same:

alter user luser quota 1g on users;

The user then needs to move the table into the new tablespace. But insert may still fail, as this demo shows. It is based on a situation where I created a PDB with only the PDBADMIN user and forgot to add any additional tablespaces.

Create a new pluggable database:

create pluggable database blogtest 
  admin user pdbadmin identified by blogtest 
  storage (maxsize 10g)
  tempfile reuse
  file_name_convert=('pdbseed','blogtest')
  create_file_dest='/opt/oracle/oradata/ORCLCDB/';

alter pluggable database blogtest open;
alter session set container=blogtest;
grant resource to pdbadmin;

Connect to the new database as PDBADMIN.

create table foo (
        id number generated always as identity, 
        bar varchar2(42), 
        constraint foo_pk primary key(id));

Note that Oracle does not complain yet ¹. But when I try to insert a row:

insert into foo (bar) values ('failure');

I get:

ORA-01950: no privileges on tablespace ‘SYSTEM’

OK, back to SYS (or SYSTEM):

create tablespace users 
    datafile '/opt/oracle/oradata/ORCLCDB/blogtest/users01.dbf' 
    size 100M autoextend on next 1g;
alter user pdbadmin default tablespace user quota unlimited on users;

In the PDBADMIN session I move the table like this and retry the insert:

alter table foo move tablespace users;
insert into foo (bar) values ('failure');

Still the same error. Why? The table has a primary key constraint and Oracle created an index for it in the same tablespace, you can check it with:

select index_name,tablespace_name from user_indexes;

The fix is:

alter index foo_pk rebuild tablespace users;