Re: Dropping empty fragmented tablespace

I'm not certain it's a delete ... because a delete of 800000 record from a table doesn't take the time this is taking ( 1 hour for each 1000 ...) i guess it is coalescing ( wich takes a lot longer ...)

About the pain ... it is the destiny of the DBA to suffer ... :-)

"Telemachus" <telemachus_at_ulysseswillreturn.net> wrote in message news:cHra9.11483$zX3.19386_at_news.indigo.ie...
> 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:26:22 CDT