Idea stolen from Uwe Hesse

I saw this in a comment by Uwe Hesse on Tanel Poder’s blog and tested it on Oracle 11.2.0.2.7. With deferred segment creation, if a user with no quota creates a table, no error will be returned. If the user later tries to insert rows into that table, the statement will fail with an ORA-01536 error. You can see what I mean in the example below, which shows the old behaviour first followed by the new behaviour: 

SQL> conn / as sysdba

Connected.

SQL> create user andrew

  2  identified by reid

  3  default tablespace users

  4  quota 0 on users

  5  /

 

User created.

 

SQL> grant

  2  alter session,

  3  create session,

  4  create table,

  5  select any dictionary to andrew

  6  /

 

Grant succeeded.

 

SQL> conn andrew/reid

Connected.

SQL> alter session set deferred_segment_creation = false

  2  /

 

Session altered.

 

SQL> create table t1 as select * from dba_tables where 1=2

  2  /

create table t1 as select * from dba_tables where 1=2

                                 *

ERROR at line 1:

ORA-01536: space quota exceeded for tablespace 'USERS'

 

SQL> alter session set deferred_segment_creation = true

  2  /

 

Session altered.

 

SQL> create table t1 as select * from dba_tables where 1=2

  2  /

 

Table created.

 

SQL> insert into t1 select * from dba_tables

  2  /

insert into t1 select * from dba_tables

            *

ERROR at line 1:

ORA-01536: space quota exceeded for tablespace 'USERS'

 

SQL>