Mismatch Between Free Space Reported from DBA_DATA_FILES - DBA_SEGMENTS and DBA_FREE_SPACE (Doc ID 416744.1)

Last updated on JULY 17, 2016

Applies to:

Oracle Database - Enterprise Edition - Version 9.2.0.1 to 11.2.0.1 [Release 9.2 to 11.2]
Information in this document applies to any platform.


Symptoms

There is a mismatch between free space reported from DBA_DATA_FILES ,  DBA_SEGMENTS and DBA_FREE_SPACE:

For a given tablespace, DBA_DATA_FILES.sum(bytes) - DBA_SEGMENTS.sum (bytes) > DBA_FREE_SPACE.sum (bytes) :

SQL> select sum(bytes) from dba_data_files where tablespace_name='USERS';

SUM(BYTES)
----------
1377566720
SQL> select sum(bytes) from dba_segments where tablespace_name='USERS';

SUM(BYTES)
----------
1349582848
SQL>  select sum(bytes) from  dba_free_space where tablespace_name='USERS';

SUM(BYTES)
----------
27918336

SQL> select 1377566720 - 1349582848 from dual;

1377566720-1349582848
--------------------- 27983872

27983872 is greater than  27918336

Which information is the correct one ?

 

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms