The Oracle (tm) Users' Co-Operative FAQ

My locally managed tablespaces seem to have lost some space according to dba_free_space. Where has it gone ?

Author's name: Jonathan Lewis

Author's Email:

Date written: 15th Dec 2002

Oracle version(s): 8 - 9

When I create a locally managed tablespace, I always seem to lose some space when I check view dba_free_space. Where has it gone ?

Back to index of questions

A locally managed tablespace breaks each file into uniform chunks, but reserves the first 64K (possibly the firsty 128K if you use a 32K block size) for the file header and space management tasks.

In the case of an autoallocate tablespace, these chunks are 64K each, but in the case of a uniform tablespace, you can specify the chunksize when you create the tablespace. Each chunk will then be available for use as an extent (although in autoallocate tablespaces, the extent sizes will be selected from 64K, 1MB, 8MB, 64MB, 256MB, and each extent will be created from a number of adjacent 64K chunks).

If you create a file that is not 64K plus an exact multiple of the chunk size, then there will be a bit of space at the end of the file which will be too small to act as a valid chunk. This will simply disappear from the dba_free_space view - which only records the total available legal chunk space for LMTs.

For example, if you create a tablespace as 'space management local uniform extent 10M' with a file size of 100M, then you will find that you have 9 extents of 10M available, a 64K header, and 9.9MB 'vanished'. Add 64K to the file, and you will suddenly find that you have a whole extra 10MB extent available.

Personally, I tend to keep the arithmetic simple, but not waste too much space, by working out a 'reasonable' size for the file, and then adding an over-generous 1MB for the header. . e.g, Extent size = 8M, so I start with (say) 800MB for data space, but make is 801MB for the file.

Further reading: N/A

Back to top

Back to index of questions