Oracle database Troubleshoot - difference between Freespace, Size Of Tablespace And Usedspace

The Problem

The free space as shown by the dba_free_space seems incorrect. The total space used by all the segments in the tablespace plus the free space does not equals to the total size of the tablespace. The tablespace is a locally managed tablespace with unifrom extent size.

For example:

SQL> create tablespace xyz datafile 'i:\temp\xyz.dbf' size 2097280K
2 DEFAULT COMPRESS
3 EXTENT MANAGEMENT LOCAL UNIFORM SIZE 50m
4 SEGMENT SPACE MANAGEMENT MANUAL;

Tablespace created.

SQL> select * from dba_tablespaces where tablespace_name = 'XYZ';

TABLESPACE_NAME BLOCK_SIZE INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS
------------------------------ ---------- -------------- ----------- -----------
MAX_EXTENTS PCT_INCREASE MIN_EXTLEN STATUS CONTENTS LOGGING FOR
----------- ------------ ---------- ---------- --------- --------- ---
EXTENT_MAN ALLOCATIO PLU SEGMEN DEF_TAB_
---------- --------- --- ------ --------
XYZ 8192 52428800 52428800 1
2147483645 0 52428800 ONLINE PERMANENT LOGGING NO
LOCAL UNIFORM NO MANUAL ENABLED

SQL> select tablespace_name, sum(bytes) from dba_data_files where tablespace_name =
2 'XYZ' group by tablespace_name;

TABLESPACE_NAME SUM(BYTES)
------------------------------ ----------
XYZ 2147614720

SQL> select tablespace_name,sum(bytes) from dba_free_space where tablespace_name =
2 'XYZ' group by tablespace_name;

TABLESPACE_NAME SUM(BYTES)
------------------------------ ----------
XYZ 2097152000

Free space lost: 2147614720 - 2097152000 = 50462720 (approx 48mb)

So, in this particular case per datafile approx 48MB is lost, if you have number of datafiles files in this tablespace the wastage would go up ( 48m * no.of datafiles).

The Solution

When creating tablespaces with a uniform extent size, it is important to understand that Oracle tries to reserve at least 64 Kbytes per datafile for the metadata blocks, which includes both file space header as well as bitmap header. When creating large database files, add an additional 64 Kbytes to the size of your datafile.

Ensure that datafile is multiple of your extentsize + 64k for header.

SQL> create tablespace xyz datafile 'i:\temp\xyz.dbf' size 2099264k reuse (-->2050m+64k)
2 DEFAULT COMPRESS
3 EXTENT MANAGEMENT LOCAL UNIFORM SIZE 50m
4 SEGMENT SPACE MANAGEMENT MANUAL;

SQL> select tablespace_name, sum(bytes) from dba_data_files where tablespace_name =
2 'XYZ' group by tablespace_name;

TABLESPACE_NAME SUM(BYTES)
------------------------------ ----------
XYZ 2149646336

SQL> select tablespace_name,sum(bytes) from dba_free_space where tablespace_name =
2 'XYZ' group by tablespace_name;

TABLESPACE_NAME SUM(BYTES)
------------------------------ ----------
XYZ 2149580800

So, 2149646336 - 2149580800 = 65536.

With this we get uniform extents of size 50m and 64k space for header. If we had kept datafile size as 2050m, 64k would have made one extent non-uniform and the remaining space as wastage.