Re: Dropping empty fragmented tablespace

Correct me if I'm wrong but doesn't this all mean that at some point there has to be a v.large delete from fet$ ? (driven from oracle internals of course but anyway...)

If this is true you will have to take the pain at some point.

"Eduardo Tavares" <ejtavares_at_tmn.pt> wrote in message news:ntra9.628$Me3.1549381_at_newsserver.ip.pt...
> 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:20:40 CDT