17 | 10 | 2017
Latest Articles
Popular Articles

Administration

Deferred Segment Creation

User Rating:  / 0
PoorBest 

Deferred Segment Creation

 

New in Oracle 11G R2 is that by default the initial extent for segments only gets created when the first row is inserted. This is absolutely not my favourite new feature.

 

STEP 1 : We create a user, grant the create session and the create table priv for that user but do not grant tablespace quota.

 

SQL> create user I_LOVE_SEGS identified by nodeferforme default tablespace users temporary tablespace temp;
User created.

SQL> grant create session,create table to I_LOVE_SEGS;
Grant succeeded.

 


STEP 2 : We connect with the user and notice that the user can create objects in the users tablespace even though it does not have tablespace quotas for that tablespace yet.

 

SQL> connect I_LOVE_SEGS/nodeferforme
Connected.


SQL> create table T_SEG (col1 number) tablespace users;
Table created.

 


STEP 3 : We notice that the tablespace quota is validated upon initial extent creation, since it is deferred it is validated only at the first insert.

 

SQL> insert into T_SEG values (1);
insert into T_SEG values (1)
*
ERROR at line 1:
ORA-01950: no privileges on tablespace 'USERS'

 

STEP 4 : The above is new in 11G R2. There is a new parameter deferred_segment_creation which defaults to TRUE

 

SQL> connect system/secret
Connected.
SQL> show parameter deferred

NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
deferred_segment_creation         boolean     TRUE

 

STEP 5 : This parameter is modifiable at both system and session level. Initial extent allocation and tablespace quota are then validated as it was before.


SQL> connect I_LOVE_SEGS/nodeferforme
Connected.
SQL> alter session set deferred_segment_creation=false;

Session altered.

SQL> create table T_SEG_NOW (col1 number) tablespace users;
create table T_SEG_NOW (col1 number) tablespace users
*
ERROR at line 1:
ORA-01950: no privileges on tablespace 'USERS'

 

STEP 6 : The segment creation immediate attribute can be used to overrule the default behaviour

 

SQL> alter session set deferred_segment_creation=true;

Session altered.

SQL> create table T_SEG_NOW (col1 number) segment creation immediate tablespace users;
create table T_SEG_NOW (col1 number) segment creation immediate tablespace users
*
ERROR at line 1:
ORA-01950: no privileges on tablespace 'USERS'