extent management local autoallocate sizes

Here is the algorithim for calculating next extent sizes with a system managed/autoallocate exten management local tablespace.

The dependency is with the current segment size.

Segment Size Next Extent Size
less than 1m 64k
1m to 63m 1m
64m to 1023m 8m
1g or more 64m

That is for 10gR2.

Note that the above table does not apply to tablespaces which have been converted from dictionary managed in the past.

Also, the next extent size is enforced. If you want to add 1 row into a 1g table which has no free space left in its current extents, then you will need to have the full 64mb of contiguous free space in its tablespace. Otherwise you will get a space error like:

ORA-01653: unable to extend table TNAME by SIZE in tablespace TSNAME

March 13, 2009

Leave a Reply

Your email address will not be published. Required fields are marked *