Re: Reorganizing the DB.. the tricky way

Rick Denoire wrote:
>
> "Anton Buijs" <aammbuijs_at_xs4all.nl> wrote:
>
> >Non-contiguous extents? Do you mean that extent 2 is not allocating the
> >blocks beside extent 1?
>
> I mean that some free space or space allocated to a different segment
> will lie in between.
>
> >To my knowledge you can't say in with datafile (or blocks) the table must
> >allocate space. You can only specify a tablespace.
>
> But one could resize all OTHER files to a mininum, so Oracle would
> have to put the table into a specific file... Dirty trick, I guess.
>
> >My advice would be to rebuild the tables into another tablespace. And make
> >that tablespace a Locally managed tablespace (LMT).
>
> Actually, while writing this article, they are being rebuild into a
> different, locally managed tablespace. But: It has a different name. I
> would have then to change the default tablespace for all relevant
> users.
>
> The problem is that there is no legal way to rename a tablespace in
> Oracle 8.1.7.
>
> >Visit www.orapub.com and download Technical paper #104 Oracle 7 Server Space
> >Management (free registration). Although it covers V7 it is still valid
> >(maybe not on details). It gives a very good calculation of the extra IO
> >involved for a full tablescan of a table when it has allocated 5 or 1000
> >extents. The proposed space management strategy is now in fact enforced with
> >LMT.
> >|
>
> Thanks for the hint. I will check that.
>
> Bye
> Rick Denoire

You could write a really nifty database level trigger to catch errors, diagnose the error message number for a tablespace error and log the attempt and the sql

hth
connor

-- 
=========================
Connor McDonald
http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue"