resize datafile contention

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. Received on Mon Oct 15 2007 - 14:29:47 CDT