Space puzzle

[Manuel Urena]

I cannot understand why I'm getting the following:

The file is an oracle temp file. How come is the file almost 1.5GiB when the file system has only got 500MiB?


# bdf /dev/proddg/USER_TTS
Filesystem kbytes used avail %used Mounted on
/dev/proddg/USER_TTS
512000 512000 0 100% /oracle/user_tts

# ls -l /oracle/user_tts
total 1021542
drwxr-xr-x 2 root root 96 Jul 14 2004 lost+found/
-rw-r----- 1 oracle oinstall 1572872192 Aug 20 15:54 user_tts0.dbf

# lvdisplay /dev/proddg/USER_TTS
--- Logical volumes ---
LV Name /dev/proddg/USER_TTS
VG Name /dev/proddg
LV Permission read/write
LV Status available/syncd
Mirror copies 0
Consistency Recovery MWC
Schedule parallel
LV Size (Mbytes) 500
Current LE 125
Allocated PE 125
Stripes 0
Stripe Size (Kbytes) 0
Bad block on
Allocation strict
IO Timeout (Seconds) default

# du -sk /oracle/user_tts
510771 /oracle/user_tts

du(1) reports a more sensible output.

Why am I getting these confusing numbers? Would be that the file has 'holes' or something crazy like that?

I really don't know what is happening here.


Thanks for your help,

Manuel

[James R. Ferguson · Accepted solution]

Hi Manuel:

> Would be that the file has 'holes' or something crazy like that?

Yes, that's often the case for database files.

The larger value for 'ls' (1572872192) indicates the end-of-file offset whereas the 'du' value (510771 1k-blocks) indicates the actual blocks allocated.

Sparse files will evidence this relationship.

If you copy (with 'cp') the file in question as something else, you can see that the copied file is "expanded". Try this:

# dd if=/dev/zero of=/tmp/mysparse seek=1024 count=10 bs=1024
# ls -l /tmp/mysparse
-rw-r--r-- 1 root sys 1058816 Sep 1 12:15 /tmp/mysparse
# du -k /tmp/mysparse
# 32 /tmp/mysparse
# cp /tmp/mysparse /tmp/mysparse.nomore
# ls -l /tmp/mysparse.nomore
-rw-r--r-- 1 root sys 1058816 Sep 1 12:15 /tmp/mysparse.nomore
# du -k /tmp/mysparse.nomore
1040 /tmp/mysparse.nomore

Regards!

...JRF...

[Duncan Edmonstone]

Its because Oracle TEMP files are always created as sparse files... as the TEMP file is used, the file will grow... see this for more details:

http://www.orafaq.com/node/2

Are you the DBA? I would not want a DBA creating TEMP files which are bigger than the filesystem they reside on, its one of the things a DBA should be checking when creating the database or adding/modifying TEMP files.

HTH

Duncan

[Manuel Urena]

No I'm not the/a DBA.

Many thanks Duncan and James.

[Manuel Urena]

Got an answer for my question.