Re: Datafile Size

Naga,

The point is that both "ls -lh" and "du -sh" are telling the truth, just different truths based on the source data from which each utility is reporting.  The "-h" flag is not a part of this issue, by the way -- that is just a reporting option.

With regards to different truths, for source information the "ls" command is looking at the current size of the file contents, while the "du" command is looking at how much space (in file-system blocks) is consumed by those file contents.  As you can imagine, if the file contents are packed into a minimum number of blocks, there will be little or no discrepancies in the output from the two utilities.  However, if for any reason there are sparsely-populated or unpopulated blocks associated with these files, then we'll see discrepancies, perhaps on the scale you've shown.  Anyone familiar with the differences between the BLOCKS column on the DBA_TABLES view and the BYTES column on the DBA_SEGMENTS view can start to guess at the real issues behind the discrepancies.  File-systems are not very unlike the Oracle database in this respect, which is one reason why there has always been so much talk about bypassing file-systems in favor of raw devices over the years.

By the way, what about my guess?  Were the datafiles in this database created atop datafiles from a previous database using the REUSE clause during the CREATE DATABASE command?  Inquiring minds want to know....

Thanks!
Tim Gorman
consultant - Evergreen Database Technologies, Inc.
P.O. Box 630791, Highlands Ranch CO  80163-0791
website   = http://www.EvDBT.com/
email     = Tim@xxxxxxxxx
mobile    = +1-303-885-4526
fax       = +1-303-484-3608
Yahoo IM  = tim_evdbt


Nagaraj S wrote:
thanks tim for the update.  My doubt is why there is an huge filesize different only for sysaux datafile not for other datafiles.
 

 bash$ ls -lh sysaux01.dbf
-rw-rw----   1 oracle   dba         1.5G Feb 10 02:26 sysaux01.dbf
On Tue, Feb 10, 2009 at 1:43 AM, Tim Gorman <tim@xxxxxxxxx> wrote:
Naga,

My guess?  And just a guess at that...

This datafile (and possibly others) were reused from a previously-created database, using the REUSE clause in the CREATE DATABASE command.

So, some time ago, there had been another database with a "sysaux01.dbf" datafile in that directory.  I suspect this because database name is either "ORACLE" (i.e. very commonplace) or does not exist in your directory naming conventions (i.e. "/DATA/ORACLE").  So, when the first database to occupy those directories was abandoned, the datafiles were left behind.  Then, when a new database was created, the filename conflicts probably caused the CREATE DATABASE command to fail at the first try, so rather than deleting all of the existing ".dbf" files, the person running the command just said "REUSE".

I believe that your database is only using the first 310M of the larger "sysaux01.dbf" file, as the DBA_DATA_FILES and V$SYSAUX_OCCUPANTS views are telling you.  But there might be a difference between the size of a file's contents and the amount of space occupied by a file -- please see below....

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

Addressing your second question -- the discrepency between the "du -sh" command and "ls -lh" command.  From a Solaris 10 server (not sure of your OS)...
$ ls -l x.lst
-rw-r--r--   1 oracle   dba       583121 Dec  9 21:52 x.lst
$ ls -lh x.lst
-rw-r--r--   1 oracle   dba         569K Dec  9 21:52 x.lst
$ du -sh x.lst
 584K   x.lst
If the simple "ls -l" command is to be believed, then the contents of the file named "x.lst" is 583,121 bytes.  The "ls -lh" command is dividing by 1024 (to get 569.45K) and then rounding to display "569K".  So far, so good.

Apparently, the "du -sh" command is dividing by 1000 (not 1024) and then rounding upwards, even though the rules of neither financial rounding (i.e. >.5n rounds up) nor statistical rounding (i.e. >.50n, .52n, .54n, .56n, .58n rounds down, >.51n, .53n, .55n, .57n,.59n rounds up) would round the value of 583.121 up to 584 in order to display "584K".  All of this is pretty unlikely -- I don't think that a Solaris developer would do that, so I don't think this is what is happening.

The alternative explanation is that the "du" command is summarizing the number of file-system blocks consumed by the file, and not the size of the contents of the file?  In this case, it probably is not rounding by 1000 but rather counting the number of 512-byte, 1K, or ???K blocks.  The second explanation is more likely when one considers a much smaller file of only 300 bytes...
f11dd33-01:celprd5> ls -l xx.lst
-rw-r--r--   1 oracle   dba          300 Feb  9 20:03 xx.lst
f11dd33-01:celprd5> ls -lh xx.lst
-rw-r--r--   1 oracle   dba          300 Feb  9 20:03 xx.lst
f11dd33-01:celprd5> du -sh xx.lst
   1K   xx.lst
Here, we see "ls -l" and "ls -lh" showing the size as 300 bytes, but "du -sh" shows it as "1K".  This indicates that the file-system blocksize is 1K, and that is the space consumed by this file's 300 bytes.

Of course, it doesn't directly explain the discrepency between 830M and 1.5G as you're seeing, but maybe the different accounting methods might shed some insight?

Hope this helps....
Tim Gorman
consultant - Evergreen Database Technologies, Inc.
P.O. Box 630791, Highlands Ranch CO  80163-0791
website   = http://www.EvDBT.com/
email     = Tim@xxxxxxxxx
mobile    = +1-303-885-4526
fax       = +1-303-484-3608
Yahoo IM  = tim_evdbt


Nagaraj S wrote:
Also du -sh and ls -ltrh shows different output.
 
bash-3.00$ ls -ltrh
-rw-rw----   1 oracle   dba         1.5G Feb  9 08:09 sysaux01.dbf
bash-3.00$ du -sh sysaux01.dbf
 830M   sysaux01.dbf
 
 
SQL> select sum(space_usage_kbytes) from v$sysaux_occupants;
                 309504


 
On Mon, Feb 9, 2009 at 10:59 PM, Nagaraj S <nagaraj.chk@xxxxxxxxx> wrote:

Hi Gurus,

 

Kindly clarify my doubt on datafile size. I have noticed that the datafile size is different from OS level vs database. When I query to get the size of SYSAUX tablespace on database it showed me 310 MB, But on OS it showed me 830M.

 

SQL> select file_name,tablespace_name,bytes/1024/1024, maxbytes/1024/1024,AUTOEXTENSIBLE

  2  from dba_data_files where tablespace_name='SYSAUX' order by file_name;

 

FILE_NAME

--------------------------------------------------------------------------------

TABLESPACE_NAME                BYTES/1024/1024 MAXBYTES/1024/1024 AUT

------------------------------ --------------- ------------------ ---

/DATA/ORACLE/datafiles/sysaux01.dbf

SYSAUX                                     310                  0 NO

 

bash-3.00$ pwd

/DATA/ORACLE/datafiles/

bash-3.00$ du -sh sysaux01.dbf

 830M   sysaux01.dbf

bash-3.00$

 

How this is possible?
 
Regards,
Naga
-- http://www.freelists.org/webpage/oracle-l

Other related posts: