extent allocation

[nac]

I have a doubt in extents allocation.

Assume a tablespace called 'PAYROLL' having following defaults.

TABLESPACE_NAME MIN_EXTENTS MAX_EXTENTS PCT_INCREASE
--------------- ----------- ----------- ------------
PAYROLL 1 505 50

and I have created on table called TEST by logging in as a user whose default tablespace is this payroll tablespace.

create table test ( i integer)
storage (initial 1k next 1k minextents 1 maxextents 2)

Now if I run the following query

SQL> select segment_name,sum(blocks),bytes from dba_extents group by segment_name,bytes
having segment_name = 'TEST'

SEGMENT_NAME SUM(BLOCKS) BYTES
-------------------- ----------- ---------
TEST 2 16384


And the DB_BLOCK_SIZE = 8192 (8k)

I assumed that there should be the value 1 in the sum(blocks) and 8192 in the bytes column because
the block size is 8k and so there should be only one block.

But it is different and it is taking 2 blocks with 16384.

Can anyone clear this...?

[brhynold]

You have raised an interesting issue

I have followed your example exactly.
Same block size, same table creation command and here are my results


1 select segment_name,sum(blocks),(bytes/1024) from dba_extents group by segment_name,bytes
2* having segment_name = 'TEST'
3 /

SEGMENT_NA SUM(BLOCKS) (BYTES/1024)
---------- ----------- ------------
TEST 16 128

I am confused, can anyone shed light on this

[pando]

that is expected behaviour

quoted from documentation:

Keywords and Parameters
INITIAL
Specify in bytes the size of the object's first extent. Oracle allocates space for this extent when you create the schema object. Use K or M to specify this size in kilobytes or megabytes.

The default value is the size of 5 data blocks. The minimum value is the size of 2 data blocks for nonbitmapped segments or 3 data blocks for bitmapped segments, plus one data block for each free list group you specify. The maximum value depends on your operating system. Oracle rounds values up to the next multiple of the data block size for values less than 5 data blocks, and rounds up to the next multiple of 5 data blocks for values greater than 5 data blocks.

[nac]

That's Excellent...! Any more explanation...? Have any body else experienced this?

[julian]

Why do you expect only 1 block upon creation? Where do you think that the Master Free List will be placed? Oracle needs a separate block for it (exactly after the segment header, if you are curious where it is placed).

If you run OPS, the situation is a bit different if you use BLOCKING FACTOR in the init.ora parameter GC_FILES_TO_LOCKS.