Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: resize datafile contention

Re: resize datafile contention

From: Ben <>
Date: Mon, 15 Oct 2007 12:45:53 -0700
Message-ID: <>

On Oct 15, 3:39 pm, Mark D Powell <> wrote:
> On Oct 15, 3:29 pm, Ben <> wrote:
> > Ent Ed AIX5L
> > When I issue an Alter database datafile .... resize ...m; to something
> > smaller than what it is I'm seeing it blocking other sessions
> > particularly inserts. Is this normal?
> > The tablespace that the datafile resides in is dictionary managed and
> > was comprised of thousands of small extents. It was around 8500M and
> > I'm trying to resize it down to about 500M. It's taking forever
> > chomping away little bits at a time but it seems like anytime I try to
> > resize and shrink it more than 50M it is horridly slow. It is
> > currently empty as I moved all the objects to another tablespace, my
> > goal is to shrink this tablespace and create a new locally managed
> > tablespace using the freed up space on the OS. I do believe that my
> > overall problem is contention on fet$. I was going to drop the
> > tablespace after I emptied it but I can't bounce the instance and I've
> > seen in the past that our OS doesn't actually reclaim the free space
> > when you drop a datafile until after you bounce the instance. Any
> > other ideas of a quicker way to get rid of this datafile?
> > I also tried to COALESCE the tablespace after all the segments were
> > gone and saw the same blocking of inserts.
> Since the tablespace is empty why not take it offline, drop it, and
> then remove the files at the OS level? The space should be
> immediately reclaimed by the OS this way.
> You should then be able to recreate the tablespace as locally managed
> without ever having to bounce the database.
> HTH -- Mark D Powell --- Hide quoted text -
> - Show quoted text -

That was my thought, but I'm leary just because I've dropped a datafile in the past and the OS wouldn't reclaim the space until a bounce of the database.

Would setting the tablespace to offline make a difference in the OS reclaiming the space without a bounce? Received on Mon Oct 15 2007 - 14:45:53 CDT

Original text of this message