Re: Alter Table Move, specifying target block?

alter table move /alter index rebuild
tend to move the extents to the front
of the tablespace in locally managed tablespace, but not in dictionary managed tablespaces because the new location for the data segment is determined by the order in which rows are picked up from fet$. and the order that rows are selected from a table is indeterminate unless you use an ORDER BY, which Oracle does not do when searching for free extents.

Since applications need never know about tablespaces, I tend to advise people simply to move the data into a new tablespace (or several new t/s) and drop the old one.

An alternative would be to convert the DMT to an LMT then move the objects - but converted DMTs don't really make good LMTs. (And the functionality is only available from about 8.1.6 onwards).

--
Jonathan Lewis
http://www.jlcomp.demon.co.uk

Next Seminar - UK, April 3rd - 5th
http://www.jlcomp.demon.co.uk/seminar.html

Host to The Co-Operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Author of:
Practical Oracle 8i: Building Efficient Databases


Rene Nyffenegger wrote in message ...


>Hello

>

>Here's the problem that I've faced today. I needed to shring

>a tablespace. Needless to say that the tablespace was very fragmented

>and I first needed to defragment it a little. Working on 8i, I decided

>that is what 'alter table move' and 'alter index rebuild tablespace <same

>ts>' is for. I expected the moved segments to go towards the tablespace's

>beginning, but was wrong. It ended that I moved the segments to

>another tablespace, then shrunk the tablespace and moved them back.

>

>The question is: is there a better way than that?

>

>Rene

>

>

>

>--

>Recherchen im schweizerischen Handelsregister:

>http://www.adp-gmbh.ch/SwissCompanies/Search.php3

>