Re: Datafile Size

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/ <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
>>
>
>

Other related posts: