Re: Moving datafiles, freeing space

From: Chuck <chuckh_nospam_at_softhome.net>
Date: 4 Aug 2004 20:34:39 GMT
Message-ID: <Xns953BA8A279EFEchuckhnospamsofthome@130.133.1.4>

joel-garry_at_home.com (Joel Garry) wrote in news:91884734.0408031400.1f610f57_at_posting.google.com:

> Chuck <chuckh_nospam_at_softhome.net> wrote in message
> news:<Xns953A5DA207E9Achuckhnospamsofthome_at_130.133.1.4>...

>> Mladen Gogala <gogala_at_sbcglobal.net> wrote in
>> news:pan.2004.08.03.10.38.29.471246_at_sbcglobal.net: 
>> 
>> > On Mon, 02 Aug 2004 18:32:56 +0000, Chuck wrote:
>> > 
>> >> After moving an oracle datafile, it sometimes takes hours or even
>> >> days for the space to become free in the filesystem that the
>> >> datafile was moved from. Why is this? Is there a way force Oracle
>> >> to release this space without restarting the instance?
>> >> 
>> >> Platform AIX 4.3, Oracle 8.1.7.4
>> > 
>> > Unmount the file system and re-mount it again.
>> > 
>> 
>> This is a 24x7x364 production database. There are other datafiles on
>> that filesystem. Unmounting and remounting are not an option. Neither
>> is bouncing the instance which also frees up the space immediately.

>
> http://ftp.cerias.purdue.edu/pub/tools/unix/sysutils/lsof/
>
> Perhaps you can use this to prove an Oracle process has the former
> file open, or conversely, that Sybrand is right.
>
> Also see
> http://groups.google.com/groups?selm=74oc51%243ne%241%40nnrp1.dejanews.
> com&output=gplain
>
> jg
> --
> @home.com is bogus.
> I think nothing on the internet will be secure without a strong
> non-repudiation policy built into the underlying protocol.
> http://catless.ncl.ac.uk/Risks/23.47.html#subj6
>

Prior to moving the file about 20 oracle processes had it open. 19 of them were Oracle background processes (dbw0, ckpt, etc.) Immediately after moving it only 1 did and that was a user's shadow process. The original filesystem's free space remained the same before and after moving it. I decided to try forcing a checkpoint to see if that would release the space and it did. Was it coincidence? Maybe. If I can consistently release the space with a checkpoint though I will be inclinded to think it's an Oracle problem.

Could it be a unix problem? Maybe. But I've seen it on every Oracle platform I've ever used, including Windows.

One other thing I want to try is to copy the file instead of moving it, then cat /dev/null over top of the original before rm'ing it. Not sure if this will prove it's a unix problem or not but if it consistently releases the space who cares. That's the real objective.

-- 
Chuck
Remove "_nospam" to reply by email

Received on Wed Aug 04 2004 - 15:34:39 CDT