Thursday, October 18, 2012

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>

No comments: