Question:
I have an Oracle 11g instance, but I am running into an
unexpected quota error.
I have created a user. I have granted create session and
create table to the user. The user can create tables anywhere
except on tablespace system.
How is this possible?
Answer: I will hazard a guess
that you are on 11g R2 and are probably bumping into a known issue
with
deferred segment creation.
Oracle users have noted the following regarding quota issues:
Quota errors have resulted when an insert is
issued against a table created against a tablespace with deferred
segment creation enabled. The table is reported as created, but
with deferred segment creation, there are no resulting segment
creations at the time of table creation. This allows tables to be
defined against any tablespace regardless of quotas. Therefore, the
quota does not become an issue until the insert is attempted.
In 11g R2, the default for deferred segment creation is TRUE.
Try setting deferred segment creation to FALSE prior to creating the
tablespace using the following:
set deferred_segment_creation = FALSE
Then try your process again.
