Re: Reorganizing the DB.. the tricky way

"Rick Denoire" <100.17706_at_germanynet.de> wrote in message news:a1n09vobklb122f9nnhfanvc7f8g4no7ki_at_4ax.com...
> "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.

This would be normal, and would not normally be somehing to worry about. In fact the second situation (two extents from different segements next to each other in Oracle's view of the disk - and maybe even on disk) will always happen and is never ever a problem. Ever. (Did I say it wasn't a problem). You only need to worry about the first situation (free space dotted here there and everywhere) if objects can't use the free space, this can be avoided entirely by using uniform extent allocation in locally managed tablespaces (or by enforcing the equivalent policy on your developers for DMTs).

> 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.

spool off the result of SELECT 'ALTER USER '||USERNAME||' DEFAULT TABLESPACE '||<NEW_TABLESPACE_NAME>||';' FROM DBA_USERS WHERE USERNAME NOT IN ('SYS','SYSTEM'); to a sql script.

--
Niall Litchfield
Oracle DBA
Audit Commission UK
*****************************************
Please include version and platform
and SQL where applicable
It makes life easier and increases the
likelihood of a good answer
******************************************