OraFAQ Forum: Server Administration » Space allocation in a segment

Home » RDBMS Server » Server Administration » Space allocation in a segment

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Space allocation in a segment [message #189790]

Sun, 27 August 2006 12:00

Anand Ramaswamy
Messages: 111
Registered: January 2005

Senior Member

Hi All,
I am somewhat confused about the space allocation in a segment.

1)I created a table table1
create table table1(id number);

2)SELECT Bytes, Blocks, Initial_Extent, Next_Extent fROM DBA_SEGMENTS WHERE SEGMENT_NAME='TABLE1'

Bytes           Blocks          Initial         Next
131072           16               131072         131072

3)Analyze table TABLE1 compute statistics;

4)SELECT INITIAL_EXTENT, NEXT_EXTENT, BLOCKS, EMPTY_BLOCKS FROM DBA_TABLES WHERE TABLE_NAME='TABLE1';

Initial             Next              Blocks         Empty
131072               131072             0             16

5)I inserted a single row

INSERT INTO TABLE1 VALUES(1);
COMMIT;

6)Analyze table TABLE1 compute statistics;

7)SELECT INITIAL_EXTENT, NEXT_EXTENT, BLOCKS, EMPTY_BLOCKS FROM DBA_TABLES WHERE TABLE_NAME='TABLE1';

Initial             Next              Blocks         Empty
131072               131072             13             3

I would like to know how a single record can occupy 13 blocks. Can someone give a detailed description how this space allocation happens.

Thanks in advance
Anand

Report message to a moderator

Re: Space allocation in a segment [message #191270 is a reply to message #189790]

Tue, 05 September 2006 07:46

kimant
Messages: 201
Registered: August 2006
Location: Denmark

Senior Member

Very good question.

You did not list version, tablespace info etc.

On my database, this behaviour does not occur:

Br
Kim

Ps. I am using 10gR2, LMT, manual space management.

SQL>
SQL> CREATE TABLE table1(ID NUMBER);

Tabel er oprettet.

SQL>
SQL> SELECT Bytes, Blocks, Initial_Extent, Next_Extent FROM DBA_SEGMENTS WHERE SEGMENT_NAME='TABLE1'

BYTES BLOCKS INITIAL_EXTENT NEXT_EXTENT
---------- ---------- -------------- -----------
65536 8 65536

SQL>
SQL> ANALYZE TABLE TABLE1 COMPUTE STATISTICS;

Tabel er analyseret.

SQL>
SQL> SELECT INITIAL_EXTENT, NEXT_EXTENT, BLOCKS, EMPTY_BLOCKS FROM DBA_TABLES WHERE TABLE_NAME='TABL

INITIAL_EXTENT NEXT_EXTENT BLOCKS EMPTY_BLOCKS
-------------- ----------- ---------- ------------
65536 0 7

SQL>
SQL>
SQL> INSERT INTO TABLE1 VALUES(1);

1 r�kke er oprettet.

SQL>
SQL> COMMIT;

Bekr�ftelse er udf�rt.

SQL>
SQL> ANALYZE TABLE TABLE1 COMPUTE STATISTICS;

Tabel er analyseret.

SQL>
SQL> SELECT INITIAL_EXTENT, NEXT_EXTENT, BLOCKS, EMPTY_BLOCKS FROM DBA_TABLES WHERE TABLE_NAME='TABL

INITIAL_EXTENT NEXT_EXTENT BLOCKS EMPTY_BLOCKS
-------------- ----------- ---------- ------------
65536 1 6

Report message to a moderator