Home > Oracle > Delayed Segments creation

Delayed Segments creation


Usually when you create a table number of extents equal to MINEXTENTS will get created automatically.  Now it got changed, it will create extents only when it is required.

My new TEST table  got no extents just after creation 

SQL> create table TEST (name varchar2(20));

Table created.

SQL> select bytes/1024,extents from user_segments where segment_name=’TEST’;

no rows selected

I have no extents soon after the table creation, try inserting records to the table

SQL> insert into test values(‘Thomas’);

1 row created.

SQL> commit ;

Commit complete.

SQL> select bytes/1024,extents from user_segments where segment_name=’TEST’;

BYTES/1024    EXTENTS
———-              ———-
        64                 1

So, the segment got created only when it is required, not along with the table creation.  Beware when you check a table with zero recors thru *_SEGMENTS. 

This bahaviour can be disabled  or enable by the parameter  deferred_segment_creation
SQL> show parameter segment

NAME                                 TYPE        VALUE
———————————— ———– ———–
deferred_segment_creation            boolean     TRUE

Advertisements
Categories: Oracle Tags: ,
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: