dropping tablespaces and datafiles

10.2.0.2 Ent Ed AIX5L 64bit

What would be the best way to drop a dictionary managed tablespace and datafiles that has a few segments with somewhere around 1000 extents?

drop all the objects first then drop tablespace & datafiles? drop objects, resize datafiles, drop tabspcs & datafiles? drop tablespace x including contents and datafiles? truncate objects, drop objects, drop tablespace? alter table x move new_tbspc, then drop old tbspc?

I'm trying to get our database back up to par after using dmt's for the past 5 years and not bothering with extent sizes. This particular schema isn't as bad as others when it comes to number of extents. One of our schemas has around 7000 segments with 100 of those having over 1000 extents and the largest of which having 9000 extents. I found that when trying to drop some tables there is indeed a lock that blocks other space related ddl from being executed and thus causing some session to get hung.

When using the 'alter table x move' statement, do you get the same locking related to uet$ and fet$ as when you drop objects? Received on Fri Dec 07 2007 - 14:32:39 CST