Skip to Main Content
  • Questions

  • dba_data_files 'sum(bytes)' very different to actual data file size

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Kelvin.

Asked: February 16, 2022 - 8:07 am UTC

Last updated: April 08, 2022 - 2:31 am UTC

Version: 19.3.0.0.0

Viewed 1000+ times

You Asked

Hi,
I have some databases on servers with limited disk space. Some of the tablespaces are alerting that they are near full, however when I compare the size of the tablepsace using 'dba_data_files' and the acutal data file on disk using OS commands they are very different. For Example:

SQL> column dummy noprint
compute sum of "Size MB" on dummy
break on dummy
select null dummy, TABLESPACE_NAME, file_name, sum(bytes)/1024/1024 "Size MB"
from dba_data_files
where TABLESPACE_NAME in ('KPM_DATA','KPM_INDEX')
group by TABLESPACE_NAME, file_name;SQL> SQL> SQL>   2    3    4

TABLESPACE_NAME                FILE_NAME                                                       Size MB
------------------------------ ------------------------------------------------------------ ----------
KPM_INDEX                      /oracle/d01/oradata/AEJS/KPM_INDEX.dbf                            13500
KPM_DATA                       /oracle/d01/oradata/AEJS/KPM_DATA01.dbf                           17500
                                                                                            ----------
                                                                                              <b>31000</b>

SQL> ! du -shc /oracle/d01/oradata/AEJS/KPM_*.dbf
5.6G    /oracle/d01/oradata/AEJS/KPM_DATA01.dbf
5.5G    /oracle/d01/oradata/AEJS/KPM_INDEX.dbf
<b>11G     total</b>

SQL>


Can you please explain why there is such a large difference between what size the database thinks the tablespace is and what the OS says the data files are?
I have even expdp/impdp the tablespaces in an attempt to defragment the tables but there is no change.


Thanks
Kelvin

Hi,

Thanks for the fast response. Below is what you requested:


  1  select tablespace_name, file_name, bytes
  2  from dba_data_files
  3* where TABLESPACE_NAME in ('KPM_DATA','KPM_INDEX')
  4  /

TABLESPACE_N FILE_NAME                                               BYTES
------------ ---------------------------------------- --------------------
KPM_DATA     /oracle/d01/oradata/AEJS/KPM_DATA01.dbf        18,350,080,000
KPM_INDEX    /oracle/d01/oradata/AEJS/KPM_INDEX.dbf         14,155,776,000

SQL> ! ls -l /oracle/d01/oradata/AEJS/KPM_*.dbf
-rw-r-----. 1 oracle oinstall 18350088192 Feb 17 12:56 /oracle/d01/oradata/AEJS/KPM_DATA01.dbf
-rw-r-----. 1 oracle oinstall 14155784192 Feb 17 12:56 /oracle/d01/oradata/AEJS/KPM_INDEX.dbf


I can now see that they are stored as sparse files. What I dont understand is why? I thought only temporary tablespaces are stored as sparse files.

thanks
Kelvin

and Connor said...

Can you do this for me:

select tablespace_name, file_name, bytes
from dba_data_files
where TABLESPACE_NAME in ('KPM_DATA','KPM_INDEX')

ls -l /oracle/d01/oradata/AEJS/KPM_*.dbf

==========================

Thanks for your patience - unfortunately this one fell through the cracks.

Yes I agree they look like sparse files, and thats a bad thing, because we don't do that (except for TEMP). I suspect at some point those files have been copied, or amended in some way at the OS level to be sparse. I'd look at copying them during an outage back to "full size"

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

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Administration

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