Oracle: "no privileges" error when creating table with SEGMENT CREATION IMMEDIATE, success with DEFERRED - why?

Question

I am recreating the schema of a database (without its data) on a local instance of Oracle 21c XE instance, using the DDL scripts exported with SQL Developer. The CREATE TABLE statements include the option SEGMENT CREATION IMMEDIATE. When attempting to run them, I get an error: SQL Error [1950] [42000]: ORA-01950: no privileges on tablespace 'TABLESPACENAME'. If I change from SEGMENT CREATION IMMEDIATE to SEGMENT CREATION DEFERRED, the statement succeeds. Why is this happening? The user has supposedly been granted the necessary privileges. I have already ran the command GRANT RESOURCE TO superuser; on the XEPDB1 instance.

Answer 1

Because with deferred segment creation, not storage is allocated in the tablespace when the table is created, only when the first data is inserted.

From the documentation:

By default, the database uses deferred segment creation to update only database metadata when creating tables, indexes, and partitions.

When a user inserts the first row into a table or partition, the database creates segments for the table or partition, its LOB columns, and its indexes. Deferred segment creation avoids using database resources unnecessarily. For example, installation of an application can create thousands of objects, consuming significant disk space. Many of these objects may never be used.

So with the successful table creation, you will get the same error when you try to insert data, as seen in this db<>fiddle:

insert into t42 (id) values (1);

ORA-01950: no privileges on tablespace 'SYSTEM'

(using SYSTEM just to demonstrate; don't create objects in there...)

You said: "The user has supposedly been granted the necessary privileges."

This isn't about privileges, it's about having a quota allocated in the tablespace(s). You haven't shown the full create statements so we don't know if you included storage clauses to specify the tablespaces to use, or if you're just using the owner's default tablespace.

From the documentation again:

Grant to users who will be creating tables, clusters, materialized views, indexes, and other objects the privilege to create the object and a quota (space allowance or limit) in the tablespace intended to hold the object segment.

and

By default, a user has no quota on any tablespace in the database. If the user has the privilege to create a schema object, then you must assign a quota to allow the user to create objects.

So in addition to granting privileges you will need to assign a quota, with something like:

alter user USERNAME quota 1M on TABLESPACENAME

providing the owner name, tablespace name, and an appropriate limit on the amount of space they are expected to consume, or UNLIMITED if that is appropriate - probably only if the tablespace has been created specifically for that user's objects.

If the user will create objects in more than one tablespace then they will need to have a quota assigned in each one.

Read more