Dropping empty fragmented tablespace

Hi Everyone,

I have a "little" problem ... i have a 40 GB empty tablespace but very fragmented (aprox. 830000 extents ...), the reason i got this is because our guys at development made a process that creates tables of several GB with initial and next of 40K .... (lots of big tables) * (lots of small extents) = big mess ...

I've managed to recreate the segments in a new tablespace and drop all the mess in the old tablespace ( it only took me 9 months ... :-) ... this is a 2 TB production DW ... and i can't interfere with production processes ...)

When i finally droped all the segments i tryed to drop the tablespace and Oracle reserved for me the surprise of coalescing the free space when all i wanted was to drop the tablespace .... i cant understand why does Oracle need to coalesce the tablespace if i'm dropping it ....

I've tried a few aproaches to this problem ... none of them with good results .... here is what i've tried:

Fist I thoght of killing all the guys in Development ... but that wouldn't solve the problem ... :-)

Then :

I tried to Drop the tablespace .... Result .... Has to coalesce in the back ....
I tried to downsize the tablespace's datafiles .... Result .... Has to coalesce in the back ....
I tried to migrate the tablespace to Locally managed ... Result ... locks fet$ and interferes with all production (locks any session that tries to use fet$) ... had to quit this aproach ... I thought of altering datafiles offline drop and then drop tablespace ... but i'm not shure i won't have the same behaviour of the drop ... and if not
... won't that leave a lot of junk in the fet$ .... ???

I'd apreciate if someone could suggest anything that i've not already tried
... because i'm getting tired of this problem ... and probably will be crazy
before i can solve it ...

If someone can help me solve this mess or explain why does Oracle need to coalesce a tablespace when all we want is to drop it ...

Thanks in advance,

Eduardo Tavares (ejtavares_at_tmn.pt) Received on Mon Aug 26 2002 - 10:05:52 CDT