Difference in Tablespace Size Values From dba_data_files and dba_tablespace_usage_metrics/V$Filespace_usage (Doc ID 455715.1)

Last updated on MARCH 03, 2017

Applies to:

Oracle Database - Enterprise Edition - Version 10.1.0.2 to 12.1.0.2 [Release 10.1 to 12.1]
Information in this document applies to any platform.
*** Checked for relevance on 6-Oct-2014 ***
*** Checked for relevance on 05-Apr-2016 ***

Symptoms

Tablespace size values differ when checked from DBA_DATA_FILES and DBA_TABLESPACE_USAGE_METRICS /V$FILESPACE_USAGE
The example shows data for a tablespace with a 16k block size.

select TABLESPACE_NAME, sum(blocks)*16384/1024/1024 "SIZE (MB)" from dba_data_files
where TABLESPACE_NAME='IA_MART_TS' group by tablespace_name;

TABLESPACE_NAME                 SIZE (MB)
------------------------------ ----------
IA_MART_TS                         164800


Vs. SUM SIZE FROM DBA_TABLESPACE_USAGE_METRICS

select TABLESPACE_NAME, TABLESPACE_SIZE*16384/1024/1024 "SIZE (MB)" from
DBA_TABLESPACE_USAGE_METRICS
where tablespace_name='IA_MART_TS';

TABLESPACE_NAME                 SIZE (MB)
------------------------------ ----------
IA_MART_TS                         160800


In this case, there is a 3.9GB difference in the values reported from both views.

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