Home » RDBMS Server » Server Administration » Space allocation in a segment
Space allocation in a segment [message #189790] Sun, 27 August 2006 12:00 Go to next message
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
Re: Space allocation in a segment [message #191270 is a reply to message #189790] Tue, 05 September 2006 07:46 Go to previous messageGo to next message
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
Re: Space allocation in a segment [message #191276 is a reply to message #191270] Tue, 05 September 2006 07:58 Go to previous messageGo to next message
Anand Ramaswamy
Messages: 111
Registered: January 2005
Senior Member
No Message Body
Re: Space allocation in a segment [message #191277 is a reply to message #191270] Tue, 05 September 2006 07:59 Go to previous messageGo to next message
Anand Ramaswamy
Messages: 111
Registered: January 2005
Senior Member
Hi,
My Oracle version is 9.2.0.6 and I am using LMT
Re: Space allocation in a segment [message #191280 is a reply to message #191277] Tue, 05 September 2006 08:03 Go to previous messageGo to next message
kimant
Messages: 201
Registered: August 2006
Location: Denmark
Senior Member
How is space allocated?
Re: Space allocation in a segment [message #191282 is a reply to message #189790] Tue, 05 September 2006 08:10 Go to previous message
Anand Ramaswamy
Messages: 111
Registered: January 2005
Senior Member
Allocation Type is SYSTEM
Previous Topic: can i know when last date and time the a DB user logged into DB ?
Next Topic: In Line Views
Goto Forum:
  


Current Time: Fri Dec 02 12:03:24 CST 2016

Total time taken to generate the page: 0.17676 seconds