Re: Free Space within Table or Index

Neil Boemio wrote:
>
> When creating a new table or index and after loading up the data, I'd
> like to be able to tell if I have sized the object too small or much too
> big. Is there any view which will tell me how much free space exists
> within a table or index?
>
> ___________________________________________
> _/_/_/ Neil Boemio _/_/_/
> _/_/_/ nboemio_at_bway.net _/_/_/
> _/_/_/ http://www.bway.net/~nboemio/ _/_/_/

select max(extent_id)+1
from sys.dba_extents
where segment_name = 'MYTABLE';

This will tell you how many extents have been alocated to the table. It won't tell you how much of the last allocated extent is used. If you sized your table for maxextents of 50 and only 10 are allocated, then you were off. If you expect NO GROWTH after your inital loading of data, then try to size it such that it uses the same number of extents as maxextents.

On the other side.... you'll know if you sized it too small. You'll get allocation errors when loading the data.

If you are interested, I have a DOS utility which takes your table structure as input and the number of rows and % free and block size, etc. and gives you an estimate of the amount of space required. I've used it for years and is has always been very close.

• TRW -- ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||| | Tim Witort || He is not silent. He is not | | trwATmedicalertDOTorg || whispering. We are not quiet | | || we are not listening. | | Pin: TZ, only one :^( || - Out of the Grey | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||