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: Mark D Powell <>
Date: Mon, 15 Oct 2007 12:39:18 -0700
Message-ID: <>

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 -- Received on Mon Oct 15 2007 - 14:39:18 CDT

Original text of this message