Difference Between Freespace, Size Of Tablespace And Usedspace (Doc ID 268574.1)

Symptoms

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.

Eg:

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).

Changes

None

Cause