Strange case of ORA-01950: no privileges on tablespace28 Jan 2019
I recently faced a strange issue where a user got an
ORA-01950: no privileges on tablespace while issuing some DML statement.
A quick look on
DBA_TS_QUOTAS showed that the segment owner (according to
DBA_SEGMENTS) has unlimited quota on the reported tablespace.
After checking the view definition of
DBA_SEGMENTS and its backing view
SYS_DBA_SEGS I became a bit suspicious because the reported owner is taken from
SYS.OBJ$. Could it be, that the segment is in fact owned by a different user?
SYS.SEG$ revealed that the owner of the segment (
USER#) is indeed a different one than the one reported by
DBA_SEGMENTS. In fact, the user did not even exist anymore.
The following query shows all segments where the segment owner does not exist anymore:
The query returned 2 rows. Both segments were non-partitioned primary key indexes on a partitioned table. Rebuilding the indexes fixed the issue but I’m still curious how this could happen.
There’s a bug on MOS (Bug 12614714) which describes a similar issue for segments for which an exchange partition operation was performed. Here’s an excerpt from the note:
ORA-1950 updating a partitioned table
The user# in seg$ is not swapped after a partition exchange when the tables involved are in different schemas and one of the segments (partition/ subpartition/lob fragment) is delayed, then it is likely that you have hit this bug.
Despite that in my case no exchange partition was performed, deferred segment creation is certainly enabled. So, I assume it has something to do with that, but I was not able to think of a scenario where this could happen, nor was I able to create a reproducible test case.