Administration
Deferred Segment Creation
User Rating: / 0
- Details
-
Parent Category: Articles
-
Created on Sunday, 08 August 2010 21:25
-
Last Updated on Friday, 04 October 2013 14:24
-
Published on Sunday, 08 August 2010 21:25
-
Written by Guy Lambregts
-
Hits: 5432
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'