Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Moving datafiles, freeing space

Re: Moving datafiles, freeing space

From: Joel Garry <joel-garry_at_home.com>
Date: 5 Aug 2004 10:48:48 -0700
Message-ID: <91884734.0408050948.6c227a2@posting.google.com>


Chuck <chuckh_nospam_at_softhome.net> wrote in message news:<Xns953BA8A279EFEchuckhnospamsofthome_at_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.

Simple answer, kill the user. :-)

Probably why I haven't seen this so much in recent years, I habitually tend to kill user unix processes and let PMON sort it out, rather than depend on SMON (ie, alter session kill, OEM), which may never get there. I think you've fingered a real reason to do that, and I guess that would explain why a checkpoint helps, like poking at SMON with an eleven-foot pole.

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

I started to type in a suggestion like this, then decided not to because I think mv/cp are actually quite vendor-dependent, going back to the days when you had to worry about which filesystem things were on. It's been quite a while since AIX for me.

jg

--
@home.com is bogus.
AIX bombshell:  http://www.forbes.com/business/2004/08/04/cz_dl_0804sco.html
Received on Thu Aug 05 2004 - 12:48:48 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US