ORA-01950 On Insert but not on Create Table

Sounds weird creating table does not raise any error, but inserting a correct tuple in this table raise a permission error, right? Just take a look:

SQL> create table matheusdba.table_test(a number) tablespace TEST_TABLESPACE;
Table created.
SQL> insert into matheusdba.table_test values (1);
insert into matheusdba.table_test values (1)
*
ERROR at line 1:
ORA-01950: no privileges on tablespace 'TEST_TABLESPACE'

It probably it’s a new user or a tablespace for which user doesn’t has quota. But why table creation doesn’t result in error but only on inserting?

Certainly the database is 11.2 or above, because this mechanism are related to deferred_segment_creation, introduced in this release. This parameter is default setted for true, and means that the segments for tables and their dependent objects (LOBs, indexes) will not be created until the first row is inserted into the table.
So, only when allocating segment for the first insert database will check privileges on tablespace.

It’s a good way to save space. But it causes too some situations when exporting with EXP, like described here.

Anyway, I think Oracle could implement segment validation when create table, it’ll avoid a lot of misunderstanding…
Now, create a table doesn’t implies in the insert will happen successfully, unless you disable the deferred_segment_creation and bring back the behavior from earlier versions:

SQL> show parameters deferred
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
deferred_segment_creation            boolean     TRUE
SQL> alter system set deferred_segment_creation=FALSE;
System altered.
SQL> create table matheusdba.table_test2(a number) tablespace TEST_TABLESPACE;
create table matheusdba.table_test2(a number) tablespace TEST_TABLESPACE
*
ERROR at line 1:
ORA-01950: no privileges on tablespace 'TEST_TABLESPACE'

See ya!
Matheus.