ORA-01950: no privileges on tablespace 'X'

Problem: A user called JIM has left your organisation, you have revoked their roles to the database, 4 days later during a restart of your application, some people start reporting 'ORA-01950: no privileges on tablespace 'ORA-01950: no privileges on tablespace X', whats happened. You have checked the owners being used in the application, and tried to insert into the table a row using the user.

INSERT INTO SIEBEL.S_EVT_ACT (
*
ERROR at line 1:
ORA-01950: no privileges on tablespace 'SIEBEL_INDEX25AS'

INSERT INTO SIEBEL.S_EVT_ACT (

ERROR at line 1:

ORA-01950: no privileges on tablespace 'SIEBEL_INDEX25AS'

They all have the roles that have permissions to the table space. They have unlimited quotas Lets look deeper to the schema

SQL&gt; select owner,table_name,tablespace_name,cluster_name,iot_name from dba_tables where table_name='S_EVT_ACT';
OWNER TABLE_NAME TABLESPACE_NAME CLUSTER_NAME IOT_NAME
------------------------------ ------------------------------ 
SIEBEL S_EVT_ACT SIEBEL_500

SQL> select owner,table_name,tablespace_name,cluster_name,iot_name from dba_tables where table_name='S_EVT_ACT';

OWNER TABLE_NAME TABLESPACE_NAME CLUSTER_NAME IOT_NAME

------------------------------ ------------------------------

SIEBEL S_EVT_ACT SIEBEL_500

SQL&gt; select owner,table_owner,table_name,index_name,index_type,tablespace_name from dba_indexes where table_name='S_EVT_ACT';
OWNER TABLE_OWNER TABLE_NAME INDEX_NAME INDEX_TYPE TABLESPACE_NAME
---------------------------------------------------------------
SIEBEL SIEBEL S_EVT_ACT S_EVT_ACT_F11 NORMAL SIEBEL_INDEX25AS
JIM SIEBEL S_EVT_ACT S_EVT_ACT_M6 FUNCTION-BASED NORMAL SIEBEL_INDEX25AS
JIM SIEBEL S_EVT_ACT S_EVT_ACT_M8 FUNCTION-BASED NORMAL SIEBEL_INDEX25AS
SIEBEL SIEBEL S_EVT_ACT S_EVT_ACT_F14 NORMAL SIEBEL_INDEX25AS

SQL> select owner,table_owner,table_name,index_name,index_type,tablespace_name from dba_indexes where table_name='S_EVT_ACT';

OWNER TABLE_OWNER TABLE_NAME INDEX_NAME INDEX_TYPE TABLESPACE_NAME

---------------------------------------------------------------

SIEBEL SIEBEL S_EVT_ACT S_EVT_ACT_F11 NORMAL SIEBEL_INDEX25AS

JIM SIEBEL S_EVT_ACT S_EVT_ACT_M6 FUNCTION-BASED NORMAL SIEBEL_INDEX25AS

JIM SIEBEL S_EVT_ACT S_EVT_ACT_M8 FUNCTION-BASED NORMAL SIEBEL_INDEX25AS

SIEBEL SIEBEL S_EVT_ACT S_EVT_ACT_F14 NORMAL SIEBEL_INDEX25AS

We revoked JIM's access when he left but he still has objects in the application schema. For sure if new extents are going to be allocated for this object, it will fail with the no privilege error. It may still work for some users because the already allocated extent space for JIM can be used without any issues.

Month: September 2015

ORA-01950: no privileges on tablespace ‘X’