Re: resize datafile contention

On Oct 15, 3:39 pm, Mark D Powell <Mark.Pow..._at_eds.com> wrote:
> On Oct 15, 3:29 pm, Ben <bal..._at_comcast.net> wrote:
>
>
>
>
>
> > 10.2.0.2 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