Re: Datafile Size
Date: Tue, 10 Feb 2009 17:02:03 +0530
Message-ID: <e921f8570902100332j6f855d7ia017837191efa54f_at_mail.gmail.com>
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_at_evdbt.com> 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_at_EvDBT.com
> 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_at_gmail.com> 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-lReceived on Tue Feb 10 2009 - 05:32:03 CST