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