Thanks for the question, Quanwen.

Asked: November 22, 2017 - 7:09 am UTC

Last updated: December 01, 2017 - 2:51 am UTC

Version: 11.2.0.4.0

Viewed 10K+ times! This question is

You Asked

Hello,

teams:-)

Why SUM(USER_BYTES) in DBA_DATA_FILES is much larger than SUM(BYTES) in dba_free_space ?

There has an example that I have given in Oracle 11.2.0.4.0.

SYS@orcl28> select round(sum(user_bytes)/(1024*1024*1024),2) from dba_data_files;

ROUND(SUM(USER_BYTES)/(1024*1024*1024),2)
-----------------------------------------
                                   495.29

SYS@orcl28> select round(sum(bytes)/(1024*1024*1024),2) from dba_free_space;

ROUND(SUM(BYTES)/(1024*1024*1024),2)
------------------------------------
                              198.57

From Oracle 11gR2 official documentation,

https://docs.oracle.com/cd/E11882_01/server.112/e40402/statviews_3143.htm#REFRN23049

I can see some description details about column USER_BYTES in DBA_DATA_FILES

USER_BYTES     NUMBER      The size of the file available for user data. The actual size of the file minus the USER_BYTES value is used to store file related metadata.

Is this SUM(USER_BYTES) in DBA_DATA_FILES equal to SUM(BYTES) in DBA_FREE_SPACE ?

Please help me for analyzing it,thanks a lot.

Best Regards,
Quanwen Zhao

and Connor said...

USER_BYTES is not about free space, but about how much of a file can be used by *you*. For example,

SQL> select bytes, user_bytes, bytes - user_bytes overhead
  2  from dba_data_files
  3  where file_name = 'C:\ORACLE\ORADATA\DB122\LARGETS.DBF';

     BYTES USER_BYTES   OVERHEAD
---------- ---------- ----------
5452595200 5451546624    1048576

So my file is 5452595200bytes in size, but in reality I will only *ever* be able to use 5451546624bytes, because I've lost 1048576 bytes in overhead that is needed to manage the file itself.

Rating

(3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

the value of USER_BYTES is equal to via BYTES subtracting METADATA

Quanwen Zhao, November 23, 2017 - 1:53 am UTC

Thank you,connor!

Now,I seems to be a little understanding,the value of USER_BYTES is equal to via BYTES subtracting METADATA(the content of managing file itself).

In the meantime,my another question is coming,how to know the bytes used about file?

Best Regards
Quanwen Zhao

November 27, 2017 - 1:18 am UTC

bytes used = bytes available - bytes free

Is the column BYTES in VIEW dba_extents also equal to bytes used ?

Quanwen Zhao, November 27, 2017 - 3:24 am UTC

Thanks,Connor

Although bytes used = bytes available - bytes free,furthermore,is the column BYTES in VIEW dba_extents also equal to bytes used ?

Now,there has an example I give below,

SYS@ysyktest> set linesize 300
SYS@ysyktest> set pagesize 300
SYS@ysyktest> col tablespace_name for a15
SYS@ysyktest> col used_gb for 99999.99
SYS@ysyktest> select tablespace_name,round(sum(bytes)/(1024*1024*1024),2) used_gb from dba_extents where tablespace_name='SYSTEM' group by tablespace_name;

TABLESPACE_NAME    USED_GB
--------------- ----------
SYSTEM                 .75

SYS@ysyktest> set linesize 300
SYS@ysyktest> set pagesize 300
SYS@ysyktest> col tablespace_name for a15
SYS@ysyktest> col total_gb for 99999.99
SYS@ysyktest> col free_gb for 99999.99
SYS@ysyktest> col used_gb for 99999.99
SYS@ysyktest> select a.tablespace_name,a.total_gb,b.free_gb,a.total_gb-b.free_gb used_gb from
  2  (select tablespace_name,round(sum(bytes)/(1024*1024*1024),2) total_gb from dba_data_files where tablespace_name='SYSTEM' group by tablespace_name) a,
  3  (select tablespace_name,round(sum(bytes)/(1024*1024*1024),2) free_gb from dba_free_space where tablespace_name='SYSTEM' group by tablespace_name) b;

TABLESPACE_NAME  TOTAL_GB    FREE_GB    USED_GB
--------------- --------- ---------- ----------
SYSTEM              20.00      19.25        .75

Best Regards
Quanwen Zhao

December 01, 2017 - 2:51 am UTC

You could use BYTES from DBA_SEGMENTS

Why it has no rows to return when I have used minus to compare to dba_segments and dba_extents ?

A reader, December 04, 2017 - 1:59 am UTC

Thanks,Connor!

VIEW dba_segments have actually included more info(eg tables|indexes|rollback...) than dba_extents,such as

SYS@ysyktest> set linesize 200
SYS@ysyktest> set pagesize 200
SYS@ysyktest> col segment_type for a25
SYS@ysyktest> select distinct segment_type from dba_segments order by 1; 

SEGMENT_TYPE
-------------------------
CLUSTER
INDEX
INDEX PARTITION
LOB PARTITION
LOBINDEX
LOBSEGMENT
NESTED TABLE
ROLLBACK
TABLE
TABLE PARTITION
TABLE SUBPARTITION
TYPE2 UNDO

12 rows selected.

According to your suggestions,I have compared to dba_segments and dba_extents,such as,

SYS@ysyktest> set linesize 300
SYS@ysyktest> set pagesize 300
SYS@ysyktest> col tablespace_name for a25
SYS@ysyktest> col used_gb for 99999.99
SYS@ysyktest> select a.tablespace_name,a.used_gb segments_gb,b.used_gb extents_gb,a.used_gb - b.used_gb minus_gb from
  2  (select tablespace_name,round(sum(bytes)/(1024*1024*1024),2) used_gb from dba_segments where tablespace_name = 'SYSTEM' group by tablespace_name) a,
  3  (select tablespace_name,round(sum(bytes)/(1024*1024*1024),2) used_gb from dba_extents where tablespace_name = 'SYSTEM' group by tablespace_name) b
  4  where a.tablespace_name = b.tablespace_name;

TABLESPACE_NAME           SEGMENTS_GB EXTENTS_GB   MINUS_GB
------------------------- ----------- ---------- ----------
SYSTEM                            .75        .75          0

By the way,when I use the keywords 'MINUS' to test it,as a result,it has no rows to return.such as,

SYS@ysyktest> select tablespace_name,round(sum(bytes)/(1024*1024*1024),2) used_gb from dba_segments where tablespace_name='SYSTEM' group by tablespace_name
  2  minus
  3  select tablespace_name,round(sum(bytes)/(1024*1024*1024),2) used_gb from dba_extents where tablespace_name='SYSTEM' group by tablespace_name;

no rows selected

SYS@ysyktest> set linesize 300
SYS@ysyktest> set pagesize 300
SYS@ysyktest> col tablespace_name for a25
SYS@ysyktest> col used_gb for 99999.99
SYS@ysyktest> select tablespace_name,round(sum(bytes)/(1024*1024*1024),2) used_gb from dba_segments where tablespace_name='SYSTEM' group by tablespace_name;

TABLESPACE_NAME             USED_GB
------------------------- ---------
SYSTEM                          .75

SYS@ysyktest> select tablespace_name,round(sum(bytes)/(1024*1024*1024),2) used_gb from dba_extents where tablespace_name='SYSTEM' group by tablespace_name;

TABLESPACE_NAME             USED_GB
------------------------- ---------
SYSTEM                          .75

Best Regards
Quanwen Zhao

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database

Breadcrumb

Question and Answer