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