Perhaps "beginners error number 2" or so ;-)) : the space to a file is released only when the last process opening the file has ended. before this, the system-call unlink() , which really destroys the files content, cannot be issued.
If you want to shorten a file to zero length, which might be written to by a background process, do:
cat /dev/null > /path/to/file
This reduces the file immediately while preserving its inode, so that the process that holds it open can still write to it.
Right now, if you have already removed the file, the only way is to identify the process still holding it open. User strace , fuser , lsof or a similar tool to identify the process(es) and then kill the process(es). As it is a Databse you might want to stop and restart the DB properly.
As an afterthought: you should stay away from doctoring on files a DB uses as long as it is up and you are not absolutely damned sure about what you do and why. This is a great way for exploding the DB into shards of unusable diskspace.
I hope this helps.
bakunin
PS: Regarding what i said above: don't worry, that happened to all of us.
Note that the "cat /dev/null" part is useless as there is nothing to cat from /dev/null in the first place. You can actually shorten a file with any command that output nothing, e.g. :
true > /path/to/file
or
: > /path/to/file
and with most shells like sh, dash, bash, ksh, zsh and the likes (actually any shell but the csh family ones), you can even skip the command and only use a redirection for the expected effect :
Thats not entirely correct. When /dev/null is read it produces a EOF-marker and this way it is made sure that the file to be shortened is well-formed (i.e. contains a end-of-file sign).
It may well be that other tools or devices do the same. I have never claimed that my method is the only one that works - just one i know for sure to work.
It's not write-ing anything which truncates the file here -- just open-ing it with mode O_TRUNC is enough, as is done by > but not >>.
I know really early versions of DOS used to use end-of-file symbols, but I've never observed such a thing in UNIX before. Is that an Oracle thing? I would have thought that'd be handled internally by the filesystem driver. Do you have more information?