Monday, December 28, 2009

DEFERRED_SEGMENT_CREATION

What is DEFERRED_SEGMENT_CREATION?
After I read A Closer Look at the New Edition By Tom Kyte on Oracle Magazine.
Historically, when we created any database object—a table, an index, anything that consumed storage—the database would create the appropriate segments and allocate at least one initial extent. This extent might be small—say, 64K—but it was allocated.
This(deferred_segment_creation) is (11gR2)initialization parameter help to save disk space and minimizes install time. (default=true)
If set to true, then segments for non-partitioned tables and their dependent objects (LOBs, indexes) will not be created until the first row is inserted into the table.

So, I did some test about it.
SYS> create user demo identified by demo;

User created.

SYS> grant create session, create table to demo;

Grant succeeded.

SYS> connect demo/demo
Connected.

DEMO> create table tb_demo (a number);

Table created.

DEMO> select segment_name,extent_id,bytes from user_extents order by segment_name;

no rows selected

DEMO> insert into tb_demo values(1);
insert into tb_demo values(1)
*
ERROR at line 1:
ORA-01950: no privileges on tablespace 'USERS'
When first row was inserted into the table, it needs segment (quota on tablespace).
DEMO> alter session set deferred_segment_creation=false;

Session altered.

DEMO> create table tb_demo2 (a number);
create table tb_demo2 (a number)
*
ERROR at line 1:
ORA-01950: no privileges on tablespace 'USERS'
After changed "deferred_segment_creation value"=false, A "create table" situation needs segment...
SYS> alter user demo quota 10M on users;

User altered.

DEMO> select segment_name,extent_id,bytes from user_extents order by segment_name;

no rows selected

DEMO> alter session set deferred_segment_creation=false;

Session altered.

DEMO> create table tb_demo2 (a number);

Table created.

DEMO> select segment_name,extent_id,bytes from user_extents order by segment_name;

SEGMENT_NAME EXTENT_ID BYTES
------------------------- ---------- ----------
TB_DEMO2 0 65536

1 row selected.
TB_DEMO2 table allocated segment, when created.
DEMO> select segment_name,extent_id,bytes from user_extents order by segment_name;

SEGMENT_NAME EXTENT_ID BYTES
------------------------- ---------- ----------
TB_DEMO2 0 65536

1 row selected.

DEMO> insert into tb_demo values(1);

1 row created.

DEMO> select segment_name,extent_id,bytes from user_extents order by segment_name;

SEGMENT_NAME EXTENT_ID BYTES
------------------------- ---------- ----------
TB_DEMO 0 65536
TB_DEMO2 0 65536

2 rows selected.
TB_DEMO table allocated segment, after first row was inserted.

A Deferred Segment Creation (DEFERRED_SEGMENT_CREATION=true) - we don't necessary quota on tablespace, before we create table.

4 comments:

Marko Sutic said...

Hi Surachart,

I've read Tom Kyte article about deferred segment creation before some time but Tom didn't said anything about quotas.

Thanks for pointing that we don't need quota on tablespace before table creation if deferred_segment_creation is true.

Cheers,
Marko

Surachart Opun said...

yes.. he didn't talk about quota. But This is a good feature (Deferred Segment Creation feature), isn't it?

Just Fun & Enjoy...

Marko Sutic said...

Yes, nice feature indeed - especially when you have application that creates lots of tables at initial installation. I think SAP is that kind of application which is widely used today.

Keep on with interesting articles ;)

Enjoy,
Marko

Surachart Opun said...

thank you

It's nice for this feature - save time for creation.

;)